Ignore unpublished comments when determining last_post

Would it seem to you that this behavior should be part of core? Ignoring spam.module entirely, if a node is updated at 11:00, and then updated with a comment at 12:00, the 12:00 is saved in there as the node's modification time. But if a comment is unpublished, shouldn't the node also be reverted back to its previous state (being 11:00)? How is the deletion of data (permanently hiding it) different from merely temporarily hiding it (ie., unpublishing it)?

Perhaps I should move request to Drupal.

Was talking about this with killes (who "frankly [doesn't] care too much about comments"), and I'm now wondering if I'm going nutty with all this talk about node and comment timestamps. We should investigate to see if tracker.module looks at /unpublished/ comment timestamps instead of just published ones. If it doesn't make a distinction, then it would theoretically be a quickie patch to a SQL statement to only check against published comment timestamps, not unpublished.

With a quick look, it looks like tracker_page() only checks node.status, and not comment.status. We should be able to put a patch in for comment.status = 1 (unpublished being 2), and that should solve our problem, right?

Apologies... "AND c.status = 0". Modifying the two queries in tracker_page() made things, seemingly, work as I desire. Can you confirm?

Odd - I had one unpublished piece of spam that I tested against (for 4.5.0, not CVS), and modifying tracker.module seemed to work as I intended. So, the following bit of SQL still shows you erroneous nodes with unpublished comments?

SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, MAX(GREATEST(n.changed, c.timestamp)) AS last_post FROM node n LEFT JOIN comments c ON n.nid = c.nid INNER JOIN users u ON n.uid = u.uid WHERE n.status = 1 AND c.status = 0 AND '1' GROUP BY n.nid, n.title, n.type, n.changed, n.uid, u.name ORDER BY last_post DESC LIMIT 10;

The above is what is actually run against my database with the proposed tracker.module change. (This code is currently active on gamegrene.com, so we can test live if need be. Submit a comment as an anonymous user with the words "poker" or "texas hold'em", and it should be a) unpublished automatically via your module, and b) not shown in the tracker.

For what it's worth, I just woke up to another flood of 150 spams, and no corruption of my recent posts.

Have you been able to confirm this though?

Patch attached for 4.5.0. Haven't tried applying it to CVS, but it needs to go there too.