Last updated March 16, 2010. Created by joshk on April 18, 2007.
Edited by gpk, add1sun, moshe weitzman. Log in to edit this page.
UPDATE: there is a function devel_rebuild_node_comment_statistics() in devel.module which will do this for you. It is fast too. Just put
<?php
devel_rebuild_node_comment_statistics();
?>in a page with PHP input format.
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:
<?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;
Comments
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)
) ;
SQL Error
This one caused a duplicate key error for me (Drupal 5.20)... The SQL mentioned above worked fine. Took 4 seconds to run on our live database with several thousand nodes and comments.
The code at
The code at http://drupal.org/node/137458#comment-1090796 looks very similar to that in devel_rebuild_node_comment_statistics() and probably suffers from the problem described in #236511: SQL bug in devel_rebuild_node_comment_statistics.
gpk
----
www.alexoria.co.uk
n.changed or n.created?
Note that for nodes without comments the above code puts n.changed into the last_comment_timestamp. This is consistent with what http://api.drupal.org/api/function/comment_enable/6 does, but is not consistent with what happens when nodes without comments are updated. These don't have their last_comment_timestamp updated to the timestamp of the latest revision - it remains as the created time.
So if a node has no comments then the last_comment_timestamp may be either the created time or the changed time.
IMO this inconsistency is a bug in core.
[update] Have opened #743804: comment_enable() should use node created time not changed time for last_comment_timestamp.
[update 2] Currently, devel_rebuild_node_comment_statistics() also uses n.changed where it should be n.created. #744438: devel_rebuild_node_comment_statistics() should use n.created not n.changed for nodes without comments.
gpk
----
www.alexoria.co.uk
Drupal 6 - Rebuild node_comment_statistics table
Here's a code based solution for Drupal 6, incorporating the timestamp change suggested by "npk". This assumes you've emptied the table, and want to rebuild the statistics for every node:
function rebuild_node_comment_statistics() {
$result = db_query("SELECT * FROM {node}");
while ($node = db_fetch_object($result)) {
// let's create a basic record for this node
db_query('INSERT INTO {node_comment_statistics} (nid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count) VALUES (%d, %d, NULL, %d, 0)', $node->nid, $node->created, $node->uid);
// we can call this function after there's a
// row for this node in the {node_comment_statistics} table
_comment_update_node_statistics($node->nid);
}
}
thanks, works great!
thanks, works great!
For How to rebuild
For How to rebuild node_comment_statistics on Drupal 7 this is the SQL. with MySQL
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.created,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 comment WHERE status=1 GROUP BY nid) AS comment_count ON comment_count.nid=n.nid
LEFT OUTER JOIN (SELECT nid, MAX(cid) AS max_cid FROM comment WHERE status=1 GROUP by nid) AS max_node_comment ON max_node_comment.nid=n.nid
LEFT OUTER JOIN (SELECT cid,uid,name,created FROM comment 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;
[at]Killua99 ~~
Thank you
You are genius, this worked good.