How to map uid in users table data to the entityid in feild table to retrieve data of the users in drupal7

Question

I would like to retrieve data related to users with the uid in users table.

But the uid is not mapped in the feild_data tables which has entity_id and entity_type.

How are these two ids mapped in drupal database 7

Answer 1

For the field_data_* tables, when the entity_type is node, the entity_id & revision_id are the foreign keys into the node table. You can use this to JOIN into the users table.

  //@TODO: Replace field_data_* with the field table in question
  $fields = db_select('field_data_*', 'f');
  $fields->join('node', 'n', "n.nid = f.entity_id AND n.vid = f.revision_id AND f.entity_type = 'node'");
  $fields->join('users', 'u', "u.uid = n.uid");
  $new = $fields
    ->fields('f')
    ->fields('u',array('uid'))
    ->execute()->fetchAll();