2

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?

  • 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 '14 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 '14 at 9:58
  • @Mołot I edited the question. – Javiator Apr 2 '14 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 '14 at 10:03
  • @Molot I usually use dpq($query) - how does kpr() differ? – Felix Eve Apr 2 '14 at 10:10
0

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!

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.