2

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->addField('t', 'tid');
$query->addField('t', 'name');
$query->addField('t', 'description');
$query->addField('COUNT(i.nid)', 'count');
$query->leftjoin('taxonomy_index', 'i', 'i.nid = t.nid');
$query->range(0, 60)->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?

2 Answers 2

4

You've got the right idea, but you need to use the addExpression method instead:

$query->addExpression('COUNT(i.nid)', 'count');

Also, I believe you need to call this method after the leftJoin for the taxonomy_index table to be in object scope.

See here for an example: https://drupal.org/node/1848358

5

@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')
  ->fields('t', array('tid', 'name', 'description'));

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

$query->addExpression("COUNT($alias.nid)", 'count');
$query->range(0, 60)->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')
  ->fields('t', array('tid', 'name', 'description'));
$query->leftjoin('taxonomy_index', 'i', 'i.nid = t.nid');
$query->addExpression("COUNT(i.nid)", 'count');
$query->range(0, 60)->groupBy("i.nid");
$query = db_select('taxonomy_term_data', 't')
  ->fields('t', array('tid', 'name', 'description'));
$query->addExpression("COUNT(i.nid)", 'count');
$query->leftjoin('taxonomy_index', 'i', 'i.nid = t.nid');
$query->range(0, 60)->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
3
  • 1
    That's fair, and it is what I would have done myself. I wrote my answer because I could not use a comment to write what I wrote.
    – apaderno
    Commented Dec 31, 2012 at 17:21
  • Thanks kiamlaluno for the more complete answer, and the clarification/correction around my second point.
    – mesch
    Commented Dec 31, 2012 at 18:08
  • I got to know the usage of %alias and $alias from this answer.
    – Sithu
    Commented Jan 1, 2013 at 4:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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