Any comments in the approval queue, even if not approved, will still make tracker show those nodes as new. This is a big problem for busy sites where many of those comments may in fact be spam -- people check out those nodes and find that there aren't any new comments.

Comments

dries’s picture

dries commented

The queries in tracker.module do not check the comments' status. Adding 'c.status = 0' to the WHERE-clause of the SQL queries has the side effect of hiding nodes with no comments. The solution is not to join comments that are not published but I'm not sure it is ANSI SQL to use something like:
... LEFT JOIN {comments} c ON (n.nid = c.nid AND c.status = 0) ...

kps’s picture

kps commented

Just in case it helps, this is a query I ended up using for a very similar purpose in a custom tracker-like module:

  "SELECT n.title, n.nid, COUNT(c.status) AS count, MAX(c.timestamp) AS latest FROM {node} n"
.   node_access_join_sql()
. " LEFT OUTER JOIN {comments} c ON n.nid = c.nid"
. " WHERE n.status = 1 AND " . node_access_where_sql()
. "     AND n.promote = 1 AND n.comment != 0 AND (c.status IS NULL OR c.status = 0)"
. " GROUP BY n.nid";

Caveat lector; I am no SQL guru and have only run this on MySQL.

dries’s picture

dries commented

Can someone test whether this works with PostgreSQL?

jasono’s picture

jasono commented
Version: 4.5.0-rc »
Assigned: Unassigned » jasono

This is still an issue in the CVS version. Right now, moderated comments, even once approved, never move the node's position in the tracker. I think perhaps the node_comment_statistics table isn't getting updated correctly when the comment is approved by a moderator.

jasono’s picture

jasono commented
Assigned: jasono » Unassigned

At the moment, the comment_save function in comment.module looks like this:

function comment_save($id, $edit) {
  db_query("UPDATE {comments} SET subject = '%s', comment = '%s', status = %d, format = '%s', name = '%s', mail = '%s', homepage = '%s' WHERE cid = %d", $edit['subject'], $edit['comment'], $edit['status'], $edit['format'], $edit['name'], $edit['mail'], $edit['homepage'], $id);
  watchdog('content', t('Comment: modified %subject.', array('%subject' => '<em>'. $edit['subject'] .'</em>')));
  drupal_set_message(t('The comment has been saved.'));
}

I think there needs to be a call to update the node_comment_statistics table, incrementing the comment count and setting the last updated timestamp either to the current time, or to the time the comment was originally posted but ONLY if this is a comment being approved. This will then correct the tracker output display.

moshe weitzman’s picture

moshe weitzman commented
Priority: Critical » Normal
boris mann’s picture

boris mann commented
Priority: Normal » Critical

Moshe, I'm setting this back to critical. Having those nodes marked as new is a real issue with the volume of spam most Drupal sites get. If it's been fixed, great, but if not, then I still consider it critical, since it renders the tracker (and other things, like subscription module which sends out false email, since the comment will never get published and the node isn't updated) useless.

Steve Dondley’s picture

Steve Dondley commented
Status: Active » Closed (fixed)

I can't recreate this problem.