WordPress Custom Query for Most Commented Posts in the Past 7 Days

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);

The query seems to get top commented posts of all time, instead of the top commented posts that have been commented on in the past week.

Thanks in advance.

---

Lonut, that takes care of the date range problem and most commented, but it wont select only the most commented posts that were commented on in the past X time interval.

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 WEEK) 
        GROUP BY wposts.ID
        ORDER BY comment_count DESC
        LIMIT 0 ,  10
 ";

EDIT: Filter on date changed from YEAR to WEEK

---

This should work:

global $wpdb;
$querystr = $wpdb->get_results("SELECT comment_count, ID,post_title 
    FROM $wpdb->posts 
    WHERE 
    post_status='publish' AND 
    post_date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 WEEK) 
    ORDER BY comment_count DESC 
    LIMIT 0 , 10");

---

You're ordering by the comment_count field of the posts table. Try ordering on the number of comments matched for the group:

ORDER BY count(*) desc