Another day, another challenge with the Drupal 7 database API. I'm trying to do something like this:

select n.nid, 
     (select count(*) as count 
          from {node_cnt} nc where nc.nid = n.nid) as node_count
from {node} n

That is, I want to get back a set of node id's and the number of times that each id appears in the table node_cnt. I'm going to be laying in some other conditions and pager stuff, so I think I need to do this with db_select. This should be simple, I suppose, but I just haven't figured out the right way to think about this stuff yet. Any advice out there? Thanks!

1 Answer 1


I don't think there's any existing method that will let you add a structured sub-query as an expression, so you're probably going to be left with something like this:

$query = db_select('node', 'n')
  ->fields('n', array('nid'));

$sub_query = '(select count(*) as count from node_cnt nc where nc.nid = n.nid)';
$query->addExpression($sub_query, 'node_count');

// Build the rest of the query.

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.