I have this so far:

// for example, $tags = array(2, 8, 18);
$query = db_select('node', 'n')
  ->fields('n', array('nid'))
  ->condition('n.type', 'post')
  ->condition('n.status', 1);
$query->join('field_data_field_tags', 'tags', 'tags.entity_id = n.nid');
$query->condition('tags.field_tags_tid', $tags, 'IN');

I need to order these results by the number of "tags" associated with each node.

I've tried many things, using addExpression() with count() statements, then an orderBy(), etc, but I can't get it right. Any help would be greatly appreciated!

  • 1
    orderBy() should work ? what problems have u faced while using addExpression(), count() statements and orderBy() ? PLs mention it – harshal Sep 13 '13 at 18:58
  • I guess I'm just not sure exactly how to use expressions in SQL. I tried adding this to my above code: $query->addExpression('SELECT count(*) FROM {taxonomy_index} ti WHERE ti.nid=n.nid', 'num_tags'); $query->orderBy('num_tags', 'DESC'); – user724228 Sep 13 '13 at 19:04
  • @user724228 please edit your last comment into question. – Mołot Sep 13 '13 at 23:01
  • The question remains the original at the top: how can I order nodes by the number of terms associated with it? The code I tried in my above comment gives the error "Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count(*) FROM taxonomy_index ti WHERE ti.nid=n.nid AS num_tags FROM node'" – user724228 Sep 15 '13 at 0:49

You should put count as alias name in the count(*) and then groupby clause can be used on that column.

$query = db_select('node', 'n')
      ->fields('n', array('nid'));
$query->addExpression('COUNT(n.nid)', 'count'); // addExpression
   $query->condition('n.type', 'post')
      ->condition('n.status', 1);
    $query->join('field_data_field_tags', 'tags', 'tags.entity_id = n.nid');   
    $query->condition('tags.field_tags_tid', $tags, 'IN');
    $query->groupBy('count', 'DESC'); //count is column name given in the addExpression
  • That gives the error: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1056 Can't group on 'count' – user724228 Sep 13 '13 at 21:05
up vote 1 down vote accepted

I was able to get this to work. Following is the code I used to generate the query:

// for example, $tags = array(2, 8, 18);
$query = db_select('node', 'n')
  ->fields('n', array('nid'))
  ->condition('n.type', 'post')
  ->condition('n.status', 1);
$query->join('field_data_field_tags', 'tags', 'tags.entity_id = n.nid');
$query->condition('tags.field_tags_tid', $tags, 'IN');
$query->addExpression('count(tags.entity_id)', 'num_tags');
$query->groupBy('nid');
$query->orderBy('num_tags', 'DESC');

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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