How to rebuild node_comment_statistics
UPDATE: there is now a link and a function in devel.module which will do this for you. It is fast too. You should enable the Devel block to see the link.
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:
<?php
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 on 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 straight forward 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.
<?php
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;
This one works better for me
This one works better for me (I'm using version 5.12):
TRUNCATE TABLE `node_comment_statistics` ;
INSERT INTO node_comment_statistics( nid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count ) (
SELECT nid, c.timestamp, name, uid, comment_count
FROM comments AS c
INNER JOIN (
SELECT MAX( timestamp ) AS timestamp, COUNT( * ) AS comment_count
FROM comments
WHERE STATUS =0
GROUP BY nid
) AS c2 ON c.timestamp = c2.timestamp
) ;
INSERT INTO node_comment_statistics (
SELECT nid,created,NULL,uid,0 FROM node WHERE nid NOT IN (SELECT nid FROM node_comment_statistics)
) ;