How to do an embedded select (with count()) with the Drupal 7 database api?

Question

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!

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.