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?
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'tcount_task_views
be available to the addition clause?addExpression
can only be simple strings with value placeholders, you can have{
or}
in them i think. drupal.stackexchange.com/questions/52510/…