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!
Update to Drupal 7
In Drupal 7:
<?php
function rebuild_node_comment_statistics() {
while ($node = db_query("SELECT * FROM {node}")->fetchObject()) {
db_insert('node_comment_statistics')
->fields(array(
'nid' => $node->nid,
'last_comment_timestamp' => $node->created,
'last_comment_name' => NULL,
'last_comment_uid' => $node->uid,
'comment_count' => 0,
)
)
->execute();
// 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);
}
}
?>
Atte.
Mario Torres
www.construcmedia.com
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.
Thank you. That worked great
Thank you. That worked great for me as well. After installing the facebook comments module this error started to pop up all over the place.
@Killua99 did you deem the
@Killua99 did you deem the cid insert unnecessary? (or did it not used to be there?)
-Merrick
I'm running Drupal version
I'm running Drupal version 7.15. In phpAdmin, when I run the above SQL script it throws an eror at line: ) AS comment_count ON comment_count.nid = n.nid LEFT OUTER JOIN (
Can anyone tell me what's wrong? I'm getting the same notice error:
Notice: Undefined property: stdClass::$comment_count in comment_node_page_additions() (line 728 of /mysite_path/modules/comment/comment.module).
Thanks!!
Ah Ha!
The "comments" table doesn't have or no longer has an (s). It's "comment." Also replace "timestamp" with "changed". It worked when I made these changes.
Here's the revised script:
TRUNCATE TABLE node_comment_statistics;INSERT INTO
node_comment_statistics
(
nid,
last_comment_changed,
last_comment_name,
last_comment_uid,
comment_count
)
SELECT
n.nid,
IFNULL(last_comment.changed,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=0 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=0 GROUP by nid) AS max_node_comment ON max_node_comment.nid=n.nid
LEFT OUTER JOIN (SELECT cid,uid,name,changed 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
Still not quite right
Fixed Drupal 7 SQL below:
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.changed,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=0 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=0 GROUP by nid) AS max_node_comment ON max_node_comment.nid=n.nid
LEFT OUTER JOIN (SELECT cid,uid,name,changed 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
----------
Rankster.co.uk - the UK's best websites as ranked by you
Not quite.
That is very helpful, but looking at the code for
_comment_update_node_statistics()it uses the node creation date if there are no comments rather than the modified date. For most people this won't matter but if you are doing a migration of nodes with and without comments it makes a difference.try
<?phpTRUNCATE 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.changed,n.created) 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=0 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=0 GROUP by nid) AS max_node_comment ON max_node_comment.nid=n.nid
LEFT OUTER JOIN (SELECT cid,uid,name,changed 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
?>
No CID
Not sure why you left out the CID, here is what I used:
TRUNCATE TABLE node_comment_statistics;INSERT INTO
node_comment_statistics
(
nid,
cid,
last_comment_timestamp,
last_comment_name,
last_comment_uid,
comment_count
)
SELECT
n.nid,
IFNULL(max_node_comment.max_cid,0) AS cid,
IFNULL(last_comment.changed,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,changed FROM comment) AS last_comment ON last_comment.cid=max_node_comment.max_cid
WHERE
n.status=1
ORDER BY
n.nid