update queries to use {node_comment_statistics}

Thank you for sharing your module. I'm using it on one of my sites with a slight modification that others may find useful.

I noticed the original SQL did not include new nodes when they had no comments (perhaps this is by design), and I also noticed it did not make use of the {node_comment_statistics} table, which can help improve query performance.

Summary of changes:

• I modified the discussions page query to select from the {node} table instead of the {comments} table so that new nodes without comments would be included.
• I used {node_comment_statistics} as the source for comment_count and the_time so that COUNT and GROUP BY no longer needed to be used in the query. This also eliminated the need to provide the extra $count_query.

Original SQL (from live_discussions_page)

  $query = 'SELECT c.nid, n.title, n.type, u.name, COUNT(c.nid) AS comment_count,
            MAX(c.timestamp) AS the_time, h.timestamp AS last_viewed
            FROM {comments} c
            LEFT JOIN {node} n ON n.nid = c.nid
            LEFT JOIN {users} u ON u.uid = n.uid
            LEFT JOIN {history} h ON h.nid = n.nid AND h.uid = ' . $uid .
            ' WHERE n.status = 1 AND c.status = 0 AND c.timestamp > ' . $timelimit .
            ' AND (h.timestamp < c.timestamp OR h.timestamp IS NULL) GROUP BY c.nid ORDER BY the_time DESC';
  $count_query = 'SELECT COUNT(DISTINCT c.nid)
            FROM {comments} c
            LEFT JOIN {node} n ON n.nid = c.nid
            LEFT JOIN {users} u ON u.uid = n.uid
            LEFT JOIN {history} h ON h.nid = n.nid AND h.uid = ' . $uid .
            ' WHERE n.status = 1 AND c.status = 0 AND c.timestamp > ' . $timelimit .
            ' AND (h.timestamp < c.timestamp OR h.timestamp IS NULL)';

  $query_result = pager_query(db_rewrite_sql($query, 'c'), $page_count, 1, db_rewrite_sql($count_query, 'c'));

Modified SQL

  $query = 'SELECT n.nid, n.title, n.type, u.name, l.comment_count AS comment_count,
            l.last_comment_timestamp AS the_time, h.timestamp AS last_viewed
            FROM {node} n
            LEFT JOIN {node_comment_statistics} l ON l.nid = n.nid
            LEFT JOIN {users} u ON u.uid = n.uid
            LEFT JOIN {history} h ON h.nid = n.nid AND h.uid = ' . $uid .
            ' WHERE n.status = 1
            AND (l.last_comment_timestamp > ' . $timelimit . ')
            AND (h.timestamp < l.last_comment_timestamp OR h.timestamp IS NULL)
            ORDER BY the_time DESC';

  $query_result = pager_query(db_rewrite_sql($query, 'c'), $page_count, 1);

If your intended functionality is to exclude nodes with no comments, then you could include an additional condition that checks for {node_comment_statistics}.comment_count > 0.

Since I'm only using the page view, I haven't modified the SQL for the block view.

Thank you again for sharing your very useful module.

JM