DB API: How to add the result of an embedded query and a retrieved value

Question

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?

Answer 1

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...