Rebuild your node_comment_statistics table

If you're like me, most of your projects are redesigns of existing sites. And if the site is already on some kind of CMS, this means importing content from the old system into Drupal, and to make it easy you'll do it with MySQL directly.

There are pros and cons about this approach, which I am learning, and one of the cons is that content created by the database may not always get plugged into all of Drupal's various tables. Sure, you got the text into node, node_revisions, can your CCK tables, but there is more than meets the eye.

After importing into Drupal, and ensuring that your content is correct, you will need to rebuild the node_comment_statistics table. A quick google search later, returned the following MySQL recipe:

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; 

I can't take credit for this, as I found it on drupal.org, but I wanted to call this out for others so that maybe it would help them get to the answer faster, or serve as a reminder that you need to run this after inserting content via MySQL directly.

Happy Database Hacking!