On this page
How to rebuild node_comment_statistics
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
You can:
- Log in, click Edit, and edit this page
- Log in, click Discuss, update the Page status value, and suggest an improvement
- Log in and create a Documentation issue with your suggestion