How to do an embedded select (with count()) with the Drupal 7 database api? 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!
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')
  -&gt;fields('n', array('nid'));

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

// Build the rest of the query.