function devel_rebuild_node_comment_statistics

Regenerates the data in node_comment_statistics table.

Technique - http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#101

File

./devel.module, line 2226

Code

function devel_rebuild_node_comment_statistics() {
    // Empty table.
    db_truncate('node_comment_statistics')->execute();
    // TODO: DBTNG. Ignore keyword is Mysql only? Is only used in the rare case
    // when two comments on the same node share same timestamp.
    $sql = "\n    INSERT IGNORE INTO {node_comment_statistics} (nid, cid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count) (\n      SELECT c.nid, c.cid, c.created, c.name, c.uid, c2.comment_count FROM {comment} c\n      JOIN (\n        SELECT c.nid, MAX(c.created) AS created, COUNT(*) AS comment_count FROM {comment} c WHERE status = 1 GROUP BY c.nid\n      ) AS c2 ON c.nid = c2.nid AND c.created = c2.created\n    )";
    db_query($sql, array(
        ':published' => COMMENT_PUBLISHED,
    ));
    // Insert records into the node_comment_statistics for nodes that are missing.
    $query = db_select('node', 'n');
    $query->leftJoin('node_comment_statistics', 'ncs', 'ncs.nid = n.nid');
    $query->addField('n', 'changed', 'last_comment_timestamp');
    $query->addField('n', 'uid', 'last_comment_uid');
    $query->addField('n', 'nid');
    $query->addExpression('0', 'comment_count');
    $query->addExpression('NULL', 'last_comment_name');
    $query->isNull('ncs.comment_count');
    db_insert('node_comment_statistics', array(
        'return' => Database::RETURN_NULL,
    ))->from($query)
        ->execute();
}