How do I add COUNT(foo.bar) to a dynamic query? I have a query like the following: 

SELECT t.tid, t.name, t.description, COUNT(i.nid) 
  FROM taxonomy_term_data t 
  LEFT JOIN taxonomy_index AS i ON i.tid = t.tid
  GROUP BY i.nid 
  LIMIT 60


I wish to write this as a dynamic query. 

This is what I come up with, but it fails on the COUNT() part.

$query = db_select('taxonomy_term_data', 't');
$query-&gt;addField('t', 'tid');
$query-&gt;addField('t', 'name');
$query-&gt;addField('t', 'description');
$query-&gt;addField('COUNT(i.nid)', 'count');
$query-&gt;leftjoin('taxonomy_index', 'i', 'i.nid = t.nid');
$query-&gt;range(0, 60)-&gt;groupBy('i.nid');


The string representation of the query is:

SELECT t.tid AS tid, t.name AS name, t.description AS description, COUNTi.nid.count AS count FROM {taxonomy_term_data} t LEFT OUTER JOIN {taxonomy_index} i ON i.nid = t.nid GROUP BY i.nid LIMIT 60 OFFSET 0


COUNTi.nid.count AS count is obviously wrong. 

How do I add COUNT(foo.bar) to a dynamic query?
@MEsch is right about the method to call, but I would rather write the code as the following.

$query = db_select('taxonomy_term_data', 't')
  -&gt;fields('t', array('tid', 'name', 'description'));

$alias = $query-&gt;leftjoin('taxonomy_index', 'i', '%alias.nid = t.nid');

$query-&gt;addExpression("COUNT($alias.nid)", 'count');
$query-&gt;range(0, 60)-&gt;groupBy("$alias.nid");


SelectQuery::leftJoin() returns the alias effectively used in the query, and the code I wrote is more generic.

As for calling SelectQuery::addExpression() before SelectQuery::leftJoin(), the result doesn't change, if you are not using the alias returned from SelectQuery::leftJoin(). The following queries return both the same result.

$query = db_select('taxonomy_term_data', 't')
  -&gt;fields('t', array('tid', 'name', 'description'));
$query-&gt;leftjoin('taxonomy_index', 'i', 'i.nid = t.nid');
$query-&gt;addExpression("COUNT(i.nid)", 'count');
$query-&gt;range(0, 60)-&gt;groupBy("i.nid");




$query = db_select('taxonomy_term_data', 't')
  -&gt;fields('t', array('tid', 'name', 'description'));
$query-&gt;addExpression("COUNT(i.nid)", 'count');
$query-&gt;leftjoin('taxonomy_index', 'i', 'i.nid = t.nid');
$query-&gt;range(0, 60)-&gt;groupBy("i.nid");


When you print $query, you get the following, in both the cases.

SELECT t.tid AS tid, t.name AS name, t.description AS description, COUNT(i.nid) AS count
FROM 
{taxonomy_term_data} t
LEFT OUTER JOIN {taxonomy_index} i ON i.nid = t.nid
GROUP BY i.nid
LIMIT 60 OFFSET 0