Re-writing SQL statement in Drupal dynamic queries

Question

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?

SELECT 
    node.title, 
    GROUP_CONCAT(field_data_field_location.field_location_value) 
FROM 
    node 
    INNER JOIN field_data_field_location 
        ON node.nid = field_data_field_location.entity_id 
    INNER JOIN 
        (SELECT DISTINCT 
            entity_id 
         FROM 
            field_data_field_location 
         WHERE 
            field_location_value IN ('Berlin', 'Stuttgart')) AS IDS
        ON node.nid = IDS.entity_id
GROUP BY 
    node.nid

---

What I currently have is the following:

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

$subquery = db_select('field_data_field_location', 'loc');
$subquery->distinct();
$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?

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');
$query->groupBy('n.nid');

$subquery = db_select('field_data_body', 'data');
$subquery->distinct();
$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!