How do I build a dynamic query in Drupal 7 equivalent to this SQL query?

Question

I want to make this SQL query into a dynamic drupal query:

SELECT name, u.uid, COUNT( n.nid ) AS total_written 
FROM users u 
LEFT JOIN node n ON n.uid = u.uid
GROUP BY u.uid
ORDER BY total_written DESC

I do not know where i should append the COUNT. This is what I tried:

$query = db_select('users', 'u');
$query->leftJoin('node', 'n', 'n.uid = u.uid');
$query
  ->fields('u', array('uid', 'name',))
  ->fields('u', array('COUNT(n.nid)', 'total_written')) # Wrong
  ->groupBy('u.uid')
  ->orderBy('total_written', 'DESC');

How do I append the COUNT to the dynamic query?

Answer 1

$query = db_select('users', 'u');
$query->leftJoin('node', 'n', 'n.uid = u.uid');
$query
  ->fields('u', array('uid', 'name',))
  ->groupBy('u.uid')
  ->orderBy('total_written', 'DESC');

// Add the COUNT expression
$query->addExpression('COUNT(n.id)', 'total_written');
$results = $query->execute();

Answer 2

You can use Query coder module to convert static query to dynamic query. Here is an excerpt about it from its project page:

Convert SQL query to Drupal Database abstraction layer code.

Answer 3

Try this one,

$query = db_select('users', 'u');
$query->leftJoin('node', 'n', 'n.uid = u.uid');
$query->fields('u', array('uid', 'name',));
$query->addExpression('COUNT(n.id)', 'total_written');
$query->groupBy('u.uid')
  ->orderBy('total_written', 'DESC');

More info at - http://drupal.org/node/310075#expressions.