More fun with the DB API: I'm tracking the number of views of instances of a particular node type: I have a table that collects entries of the viewed node's nid and the timestamp of the view (which makes sense for a variety of reasons in the situation). These nodes, being regular nodes, can also have comments.

I want to retrieve a set of nodes that is ordered by the sum of the number of views and the number of comments on that node. (Don't ask why... :) The following code almost works:

$the_tasks = db_select('node', 'n')->range(0, 5);

    $the_tasks->condition('n.type', 'whatever');

    $the_tasks->join('node_comment_statistics', 'ncs', 'n.nid = ncs.nid');

    $count_task_views = "(select count(*) as count from {vr_task_view_log} where tid = n.nid)"; 
    $the_tasks->addExpression($count_task_views, 'count_task_views');

    $sum_of_views_and_comments = "(count_task_views + ncs.comment_count)";
    $the_tasks->addExpression($sum_of_views_and_comments, 'sum_of_views_and_comments');

    $the_tasks->fields('n', array('nid', 'title'));
    $the_tasks->fields('ncs', array('comment_count'));

    $the_tasks->orderBy('sum_of_views_and_comments', 'desc');

$the_tasks = $the_tasks->execute();
$the_tasks = $the_tasks->fetchAll();

Like I said, it almost works: sum_of_views_and_comments is coming out as just the number of views -- the number of comments (which are getting successfully retrieved and returned as part of the query) aren't getting added in. I've also tried omitting the "ncs." qualifier from the addition statement, but that has no effect. Any advice on how to make it work?

  • you never add ` $sum_of_views_and_comments = "(count_task_views + ncs.comment_count)";` as an expression to your query anywhere in your code... – tenken Feb 14 '13 at 1:35
  • Duh. You're right, of course. I've corrected it in my code and above, and am now getting a PDOException when I run it: Column not found: 1054 Unknown column 'count_task_views' in 'field list': SELECT n.nid AS nid, n.title AS title, ncs.comment_count AS comment_count, (select count(*) as count from {vr_task_view_log} where tid = n.nid) AS count_task_views, (count_task_views + ncs.comment_count) AS sum_of_views_and_comments FROM {node} n INNER JOIN ... Shouldn't count_task_views be available to the addition clause? – Jim Miller Feb 14 '13 at 2:34
  • It looks like my problem here is one of MySQL and not the Drupal DB API -- I've created a minimal SQL statement that fails in the same way as the API calls above. I'm going to pursue this as a separate post focusing on MySQL, and will report back when I've figured it out. – Jim Miller Feb 14 '13 at 16:04
  • subqueries in addExpression can only be simple strings with value placeholders, you can have { or } in them i think. drupal.stackexchange.com/questions/52510/… – tenken Feb 14 '13 at 16:09

This was indeed a database question; a fix for the "unknown column" problem can be found at https://stackoverflow.com/questions/14879342/can-i-do-math-on-the-result-of-an-embedded-query-in-the-parent-query (short answer: the computing of count_task_views needs to be done in a lower-level SELECT). Back to Drupal: I have my query working as pure SQL handled through db_query; I should probably figure out how to do it via the API, but that's going to wait for awhile...

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.