2

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?

6
$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();
3

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.

  • Good one actually, but still please follow the standards of the site and Welcome to DSE! – Gladiator Feb 4 '14 at 10:19
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.