node_comment_statistics table not updating when parent node moderated

Hi,

I think this is related to a bug i found in the SQL statements in nodecomment.module, function _nodecomment_update_node_statistics:
The description of the function says:
"comment_count: the total number of ***approved/published comments*** on this node."

But the SQL select the total number of comments on ***approved/published nodes***:
-> db_query('SELECT COUNT(*) FROM {node_comments} nc INNER JOIN {node} n ON n.nid = nc.nid WHERE nc.nid = %d AND n.status = %d', $nid, 1);
-> 'SELECT nc.cid, nc.name, n.created, n.changed, n.uid FROM {node} n LEFT JOIN {node_comments} nc on n.nid = nc.cid WHERE nc.nid = %d AND n.status = 1 ORDER BY cid DESC'

See the "n.status" instead of "nc.status" in both cases?

This is why the comment_count does not increment in above's scenario.

And my problem is the other way round: I want to have a count of published comments for my node, but I get the count of *all* comments for my node.

Sorry all, please ignore my last comment, that was rubbish. I misread the SQL.

Next try :-)

My solution in #1 was wrong, but the idea was right.

Let me explain:
I posted an unpublished comment (cid 216) to node 132:

mysql> select cid, pid, nid, uid from node_comments WHERE cid=216;
+-----+-----+-----+-----+
| cid | pid | nid | uid |
+-----+-----+-----+-----+
| 216 |   0 | 132 |  37 |
+-----+-----+-----+-----+

mysql> select nid, vid, type, uid, status from node WHERE nid=216;
+-----+-----+---------+-----+--------+
| nid | vid | type    | uid | status |
+-----+-----+---------+-----+--------+
| 216 | 304 | comment |  37 |      0 |
+-----+-----+---------+-----+--------+

Now I select the countable rows to my commented node (nid 132) using the statement from nodeforum.module:

mysql> SELECT nc.cid, n.nid, n.uid, n.status FROM node_comments nc INNER JOIN node n ON n.nid = nc.nid WHERE nc.nid = 132 AND n.status = 1;
+-----+-----+-----+--------+
| cid | nid | uid | status |
+-----+-----+-----+--------+
| 180 | 132 |  37 |      1 |
| 216 | 132 |  37 |      1 |
+-----+-----+-----+--------+

The SELECT collects *all* comments to a node that has status=1.

If you want (I do that) all *published* comments to a node, you have to select all *published comment-nodes* to the parent node:

$count = db_result(db_query('SELECT COUNT(*) FROM {node} n WHERE status=%d AND n.nid IN (SELECT nc.cid FROM {node_comments} nc INNER JOIN {node} n ON n.nid = nc.nid WHERE nc.nid = %d)', 1, $nid));

This works for me.
Regards,
Jutta

This has been fixed in #462688: Handling of comment updates. The problem had been that comment counts were only being updated on INSERT, not on UPDATE. So when moving a node comment out of moderation, the comment counts weren't updated until another comment was posted.