EntityFieldQuery INNER JOIN

Question

I'd like to run a query using the EntityFieldQuery object. I need value from both the node and node_access table therefore I'd need to use INNER JOIN. From the d.o documentation I can't figure out how this is possible.

Here's what I have --

$query = new EntityFieldQuery();
$result = $query->entityCondition('entity_type', 'node')
->entityCondition('bundle', 'node_access')
->propertyCondition('type', 'external_link')
->propertyCondition('status', 1)
->fieldCondition('gid', '3', '=')
->fieldCondition('realm', 'domain_id', '=')
->fieldCondition('grant_view', '1', '>=')
->range(0,1)
->execute();

Answer 1

You can't add extra joins to an EntityFieldQuery directly (it's not supported), but you could add a tag to the query, implement hook_query_TAG_alter(), and add the join manually when the query is converted down to a standard db query.

This isn't tested but will probably get you most of the way there:

$query = new EntityFieldQuery;
$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'node_access')
  // etc
  ->addTag('MYTAG');

// get the query results as normal

And then the query alter function:

function MYDMOULE_query_MYTAG_alter(QueryAlterableInterface $query) {
  $query->join('node_access', 'node_access', 'node_access.nid = node.nid');
}

The other way to do it would be to subclass EntityFieldQuery itself and add the join, but I think the above method is simpler in this case.

Answer 2

If you're using custom properties with your own tables, the tag method won't work. You need to use subqueries instead:

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'user');

$roles_subquery = db_select('users_roles', 'ur');
$roles_subquery->fields('ur', array('uid'));
$roles_subquery->condition('rid', $my_role_id);

$query->propertyCondition('uid', $roles_subquery, 'IN');

See Need a join in an EntityFieldQuery, how about a subquery? for details.