WordPress Custom Query to get Most Commented Posts in the Past 7 Days

Question

I'm trying to do a query on wpdb to get the posts commented on most in the past week... Any ideas what I'm doing wrong?

$querystr = "SELECT comment_count, ID, post_title
        FROM $wpdb->posts wposts, $wpdb->comments wcomments
        WHERE wposts.ID = wcomments.comment_post_ID
        AND wcomments.comment_date >= CURDATE() - 7 
        GROUP BY wposts.ID
        ORDER BY comment_count DESC
        LIMIT 0 ,  10
 ";

 $pageposts = $wpdb->get_results($querystr);

Thanks in advance.

EDIT Query changed to something that sort of works I'm still not getting the date range right... it is returning the posts that are most commented on of all time, and not only the posts that have been commented on in the past week.

Answer 1

This should do it:

$querystr = "SELECT comment_count, ID, post_title
        FROM $wpdb->posts wposts, $wpdb->comments wcomments
        WHERE wposts.ID = wcomments.comment_post_ID
        AND wposts.post_status='publish'
        AND wcomments.comment_approved='1'
        AND wcomments.comment_date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 YEAR) 
        GROUP BY wposts.ID
        ORDER BY comment_count DESC
        LIMIT 0 ,  10
 ";

Answer 2

The column comment_count in the comments table is a sum of all comments on the post in question.

To get the comments for a time period, you must COUNT() the comments in each group.

$query="SELECT COUNT(comment_id) as num, ID, post_title
        FROM $wpdb->posts wposts, $wpdb->comments wcomments
        WHERE wposts.ID = wcomments.comment_post_ID
        AND wposts.post_status='publish'
        AND wcomments.comment_approved='1'
        AND wcomments.comment_date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 WEEK) 
        GROUP BY wposts.ID
        ORDER BY num DESC
        LIMIT 0 ,  10
 ";