I have the following SQL query, which I need to re-write using Drupal's dynamic query language. I'm new to Drupal development, could you please give me an example for the following SQL?

    INNER JOIN field_data_field_location 
        ON node.nid = field_data_field_location.entity_id 
            field_location_value IN ('Berlin', 'Stuttgart')) AS IDS
        ON node.nid = IDS.entity_id

What I currently have is the following:

$query = db_select('node', 'n');
$query->addField('n', 'title', 'title');

$subquery = db_select('field_data_field_location', 'loc');
$subquery->fields('loc.entity_id', array('eid'));
$subquery->condition('loc.field_location_value', $locations, 'IN');

$query->join('field_data_field_location', 'l', 'n.nid = l.entity_id');
$query->addExpression('GROUP_CONCAT(l.field_location_value)', 'locations');
$query->addJoin('INNER', $subquery, 'IDS', 'n.nid = IDS.entity_id');

$result = $query->execute();

return $result->fetchAll();

If I execute the code, I just get nothing back from Drupal. I think the problem is the subquery. Do you have any idea how I can solve that?

  • Here - have an example to work off. You should be able to work out what you need to do using that...
    – Felix Eve
    Apr 2, 2014 at 9:27
  • Where do you $query->execute() and all that? How do you fetch your data? That's an important part of code, too!
    – Mołot
    Apr 2, 2014 at 9:58
  • @Mołot I edited the question.
    – Said Savci
    Apr 2, 2014 at 10:01
  • Just before $result = $query->execute(); add something like kpr( (string) $query); - this will output your query, as a string, using Devel module. And you'll be able to confirm it is really what you wanted it to be. Or not.
    – Mołot
    Apr 2, 2014 at 10:03
  • @Molot I usually use dpq($query) - how does kpr() differ?
    – Felix Eve
    Apr 2, 2014 at 10:10

1 Answer 1


Have you turned down the error_level? because if you get nothing back from Drupal then this typically means the query executed successfully but that no results were found. Perhaps check the Reports->Recent log messages for any SQL errors logged instead. This information should help you troubleshoot future Drupal SQL endeavours.

Anyway, looking over the code, I don't think this is correct:

$subquery->fields('loc.entity_id', array('eid'));

and should probably be:

$subquery->fields('loc', array('entity_id'));

or if you want to give the field an alias, use addField:

$subquery->addfield('loc', 'entity_id', 'eid');

I've just run a similar query on a fresh database (so no custom fields) and set that fields() call correctly, and this seems to get results back:

$bundles = array("page", "blog");

$query = db_select('node', 'n');
$query->addField('n', 'title', 'title');

$subquery = db_select('field_data_body', 'data');
$subquery = $subquery->fields('data', array('entity_id'));
$subquery = $subquery->condition('data.bundle', $bundles, 'IN');

$query->join('field_revision_body', 'rev', 'n.nid = rev.entity_id');
$query->addExpression('GROUP_CONCAT(rev.bundle)', 'mybundles');
$query->addJoin('INNER', $subquery, 'IDS', 'n.nid = IDS.entity_id');

$result = $query->execute();

dpq($query); // Let's look at the query
dpm($result->fetchAll()); // Let's see all the results

Note, you'll need the devel module installed to use the dpm() and dpq() functions. Hope that can help!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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