MySQL join custom field to uid

Question

I need to export some data directly from a D7 DB. I'm not that familiar with the Drupal DB schema. Some additional profile fields have been added to users profiles and I would like to export that along with some columns from the users table.

In this case the job_title custom field data appears to be stored in the field_data_field_user_job_title table.

In that table I notice there are columns entity_type and entity_id. The value entity_type is set as user for all rows in this table.

Does this mean that the entity_id field for rows in this table where entity_type='user' can be joined to users.uid to associate the custom profile data with the correct user record?

Answer 1

If the fields you're interested in are attached to the user entity directly (i.e. not using something extra like the Profile2 module), then your assumption is 100% correct. You'll also see a field_revision_field_user_job_title, which is used to store the current revision (for entities that have that functionality).

The user entity doesn't have revisions, so in my experience if you need to go directly into tables, the field_data_* ones are the best bet.

To get as much reusability as possible, though, you should really use the EntityFieldQuery class to perform such queries. For example, if you wanted to get all users with a certain "job_title", you can use something like this:

$query = new \EntityFieldQuery();
$query->entityCondition('entity_type', 'user')
  ->fieldCondition('field_title', 'value', 'foo');

$results = $query->execute();
if (!empty($results['user'])) {
  $users = user_load_multiple(array_keys($results['user']));
}