1

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!

3 Answers 3

3

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       |       |
+------------------------+------------------+------+-----+---------+-------+
1

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
}
3
  • Thanks!, but it's not quite working -- if I add a ->groupBy('n.nid') clause, I get the right nodes, but ordered by the oldest comment, not the newest. I tried to work out what was going on, but, once again, my brain blew up. However, I think I found a solution: see the following proposed answer. If you have comments, that'd be great.
    – Jim Miller
    Feb 7, 2013 at 17:40
  • Indeed, I've updated with a fixed version, but David Thomas's solution is better anyway, I'd go with that.
    – penten
    Feb 8, 2013 at 1:58
  • "I'll take 'Drupal database tables' for $1000, Alex." "node_comment_statistics" [buzz] "What is the table I most typically forget about?" "That's CORRECT!" Excellent pointer; thanks for the help!
    – Jim Miller
    Feb 8, 2013 at 2:08
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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