How to rebuild node_comment_statistics

Last updated on
25 January 2018

Drupal 7 will no longer be supported after January 5, 2025. Learn more and find resources for Drupal 7 sites

There is a function devel_rebuild_node_comment_statistics() in devel.module which will do this for you. It is fast too. Just put

devel_rebuild_node_comment_statistics();

in a page with PHP input format.

If you won't or are not able to run the devel module, then here are the same queries that it would run. Due to the IGNORE keyword, it may only work on MySQL.

TRUNCATE TABLE node_comment_statistics;

INSERT IGNORE INTO node_comment_statistics
(
    nid,
    cid,
    last_comment_timestamp,
    last_comment_name,
    last_comment_uid,
    comment_count
)
(
    SELECT c.nid, c.cid, c.created, c.name, c.uid, c2.comment_count
    FROM
        comment c
    INNER JOIN ( SELECT c.nid, MAX(c.created) AS created, COUNT(*) AS comment_count FROM comment c WHERE status = 1 GROUP BY c.nid )
        AS c2
        ON c.nid = c2.nid AND c.created = c2.created
);

INSERT INTO node_comment_statistics
(
    nid,
    cid,
    last_comment_timestamp,
    last_comment_name,
    last_comment_uid,
    comment_count
)
(
    SELECT
        n.nid,
        0 AS cid,
        n.changed AS last_comment_timestamp,
        NULL AS last_comment_name,
        n.uid last_comment_uid,
        0 AS comment_count
    FROM
        node n
    LEFT JOIN
        node_comment_statistics ncs
        ON ncs.nid = n.nid
    WHERE
        ncs.comment_count IS NULL
);

Older methods

The following may only work properly on versions of Drupal 6 and below. It's left here for historical reference.

The longhand way

Sometimes you need to move comments around at the database level. This is easy enough, but what about the node_comment_statistics table? If this gets messed up it can really throw your site off. Here's a handy bit of PHP to rebuilt that table:

db_query("DELETE FROM {node_comment_statistics}");

$nodes = db_query("SELECT nid,uid,changed FROM {node} WHERE status=1");
while ($m = db_fetch_object($nodes)) {
  $nid = $m->nid;
 
  $comments=db_query("SELECT nid,max(cid) FROM {comments} WHERE status=0 AND nid=$nid GROUP by nid");
  if($l = db_fetch_object($comments)) {
    $cid = $l->{"max(cid)"};

    $lastcomments = db_query("SELECT uid,name,timestamp FROM {comments} WHERE cid=$cid");
    $n = db_fetch_object($lastcomments);
    $uid = $n->uid;
    $name = $n->name;
    $timestamp = $n->timestamp;
 
    $counts = db_query("SELECT count(*) FROM {comments} WHERE status=0 AND nid=$nid");
    $n = db_fetch_object($counts);
    $count = $n->{"count(*)"};
  } else {
    $timestamp = $m->changed;
    $name = NULL;
    $uid = $m->uid;
    $count = 0;
  }
  db_query("INSERT INTO {node_comment_statistics} (nid,last_comment_timestamp,last_comment_name,last_comment_uid,comment_count) VALUES ('$nid','$timestamp','$name','$uid','$count')");
}

Credit goes to ahoeben in this thread.

Single query version

Credit to MikeSchinkel.

Here's a single MySQL query that does the update (well in truth two queries, if you could the TRUNCATE), and is much easier to read (at least IMO). SQL is second nature to me so I followed the logic above came up with the following function. I've tested the SQL code using a SQL desktop client (HeidiSQL) but not actually tested the PHP yet (though it's pretty straightforward to convert the SQL to Drupalish PHP so if it doesn't work it'll probably be missing semi-colon or some such.)

Also, this is a little better than the one in the devel module as that one still does multiple queries, though not in a loop.

function rebuild_node_comment_statistics() {
    db_query('TRUNCATE TABLE {node_comment_statistics}');
    $sql =<<<SQL
INSERT INTO
    {node_comment_statistics}
(
    nid,
    last_comment_timestamp,
    last_comment_name,
    last_comment_uid,
    comment_count
)
SELECT
    n.nid,
    IFNULL(last_comment.timestamp,n.changed) AS last_comment_timestamp,
    IFNULL(last_comment.name,null) AS last_comment_name,
    IFNULL(last_comment.uid,n.uid) AS last_comment_uid,
    IFNULL(comment_count.comment_count,0) AS comment_count
FROM
    node AS n
    LEFT OUTER JOIN (SELECT nid, COUNT(*) AS comment_count FROM {comments} WHERE status=0 GROUP BY nid) AS comment_count ON comment_count.nid=n.nid
    LEFT OUTER JOIN (SELECT nid, MAX(cid) AS max_cid FROM {comments} WHERE status=0 GROUP by nid) AS max_node_comment ON max_node_comment.nid=n.nid
    LEFT OUTER JOIN (SELECT cid,uid,name,timestamp FROM {comments} ORDER BY cid DESC LIMIT 1) AS last_comment ON last_comment.cid=max_node_comment.max_cid
WHERE
    n.status=1
ORDER BY
    n.nid   
SQL;
    db_query($sql);
}   

BTW, what else is nice is that you can run this same code (without the curly braces around the table names) in a SQL client if that's easier for you. Useful when making conversion scripts (which is exactly why I wrote it.)

Direct MySQL Version:

TRUNCATE TABLE node_comment_statistics;
INSERT INTO
    node_comment_statistics
(
    nid,
    last_comment_timestamp,
    last_comment_name,
    last_comment_uid,
    comment_count
)
SELECT
    n.nid,
    IFNULL(last_comment.timestamp,n.changed) AS last_comment_timestamp,
    IFNULL(last_comment.name,null) AS last_comment_name,
    IFNULL(last_comment.uid,n.uid) AS last_comment_uid,
    IFNULL(comment_count.comment_count,0) AS comment_count
FROM
    node AS n
    LEFT OUTER JOIN (SELECT nid, COUNT(*) AS comment_count FROM comments WHERE status=0 GROUP BY nid) AS comment_count ON comment_count.nid=n.nid
    LEFT OUTER JOIN (SELECT nid, MAX(cid) AS max_cid FROM comments WHERE status=0 GROUP by nid) AS max_node_comment ON max_node_comment.nid=n.nid
    LEFT OUTER JOIN (SELECT cid,uid,name,timestamp FROM comments ORDER BY cid DESC LIMIT 1) AS last_comment ON last_comment.cid=max_node_comment.max_cid
WHERE
    n.status=1
ORDER BY
    n.nid;   

Help improve this page

Page status: No known problems

You can: