2

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?

  • Why don't you make a dev version or db backup and try? – Mołot Dec 4 '13 at 13:17
  • Yes I have, my query returns some data and it looks vaguely correct. However that doesn't prove that my assumption in how the D7 database schema operates logically is correct or not. I don't want to be joining users.uid to the entity_id column, if the entity_id column isn't actually a uid and it's actually some other ID – batfastad Dec 4 '13 at 13:23
  • But you can do it, and then see how it worked in Drupal, right? If you are expecting core developers to come here and answer, you will probably be disappointed. And all we mere mortals need to test things just like I asked :) You have reasonable theory, why not to test it? It shouldn't take more than 5 minutes of your time, as you have everything you need already on your desktop. It would be way more time consuming for any of us to do it. – Mołot Dec 4 '13 at 13:27
  • 1
    Yes I have been testing but my assumption about the logical model of the schema doesn't appear to be valid in all cases. I thought there might be some vaguely experienced Drupal developers on this site who might have more knowledge about this than I do. Apologies for the inconvenience. I will seek further advice from other channels. – batfastad Dec 4 '13 at 13:38
2

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']));
}
  • 2
    Ok that's fantastic. I was checking the _revision_ field and did notice that my modifications weren't being tracked. I had assumed that is how it would work. I'm a DB guy, not Drupal, so I will be running this export directly on the database server. Thanks for the clarification - it is much appreciated! – batfastad Dec 4 '13 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.