DB API: How to get a list of nodes ordered by highest comment creation date?

Question

The DB API is once again kicking my tail...

I've got a bunch of nodes of a certain type, each of which can have any number of comments, all in the usual way. I'm trying to do a db_select()-based query that will give me the set of nodes of that type, ordered by the creation dates of each node's comments: The first node in the set would be the one that has the most recent comment, the second node would be the one that has the second most recent comment, and so on. I'm sure / strongly suspect that this takes some sort of subquery, but I'm just not getting it right. Can anyone offer some help? Thanks!

Answer 1

The best place to query for that is the node_comment_statistics table.

It contains the relevant comment statistics such as comment count and last comment timestamp for a node.

e.g: Query for article nodes ordered by last_comment_timestamp DESC

$query = db_select('node_comment_statistics', 'c');
$query->join('node', 'n', 'n.nid = c.nid');
$query->fields('c', array('cid', 'last_comment_timestamp')); // fields
$query->fields('n', array('nid', 'title'));
$query->condition('n.type', 'article'); // e.g filter for articles only
$query->condition('n.status', 0, '>');
$query->orderBy('c.last_comment_timestamp', 'DESC'); // Order by last commented
$results = $query->execute();

foreach($results as $row) {
  drupal_set_message('<pre>'.print_r($row,1).'</pre>'); // debug
}

node_comment_statistics table schema:

mysql> desc node_comment_statistics;
+------------------------+------------------+------+-----+---------+-------+
| Field                  | Type             | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+-------+
| nid                    | int(10) unsigned | NO   | PRI | 0       |       |
| cid                    | int(11)          | NO   |     | 0       |       |
| last_comment_timestamp | int(11)          | NO   | MUL | 0       |       |
| last_comment_name      | varchar(60)      | YES  |     | NULL    |       |
| last_comment_uid       | int(11)          | NO   | MUL | 0       |       |
| comment_count          | int(10) unsigned | NO   | MUL | 0       |       |
+------------------------+------------------+------+-----+---------+-------+

Answer 2

Something like the following should work:

$query = db_select('comment', 'c');
$query->join('node', 'n', 'n.nid = c.nid');

$query->fields('c', array('nid'))
    ->addExpression('max(c.created)', 'max_created');
$results = $query->condition('n.type', 'article', '=')
    ->condition('c.status', 0,'>')
    ->condition('n.status', 0,'>')
    ->groupBy('c.nid')
    ->orderBy('max_created', 'desc')
    ->execute();

foreach($results as $record) {
    // $record->nid contains the nid of one node
}

Answer 3

After some more searching, I bumped into How to do an embedded select (with count()) with the Drupal 7 database api? which, has it happens, I had posted some time ago about a similar issue. In any case, the following seems to be working for me:

$q = db_select('node', 'n');
    $q->condition('n.type', 'whatever');

    $sub_query = "(select max(created) as max_created from {comment} c where c.nid = n.nid)";
    $q->addExpression($sub_query, 'max_created');

    $q->fields('n', array('uid', 'nid'));

    $q->orderBy('max_created', 'desc');
$q = $q->execute();

As an aside, this needs to be done as a db_select on node so I can run a pager against it. In any case, comments are welcome.