WordPress Custom Query for 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);

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.

score 2 · Answer 1 · answered Jun 25 '11 at 09:53

2

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

answered Jun 25 '11 at 09:53

Ionuț Staicu

21,360
11
51
58

Thanks for the help... almost... See Jack's answer. – rpophessagr Jun 25 '11 at 10:31

jackreichert · Accepted Answer · 2011-06-25T17:05:42.700

2

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

edited Jun 25 '11 at 17:05

answered Jun 25 '11 at 10:29

jackreichert

1,979
2
23
36

1

The order by is on `comment_count`, a column of the posts table. The value in this column will not vary by the other conditions in the where clause. I would not expect the date, status or approved filters to affect the count; they'll only filter out posts. – Andomar Jun 25 '11 at 13:27
Good point, but comment count is set by the WordPress engine based upon the number of approved comments. The status and approved filters are in place so that posts that were commented on but not approved yet will be filtered out. – jackreichert Jun 25 '11 at 14:12
If you know that the filter on `comment_approved` doesn't do anything, why did you include it? Also, the answer pretends to filter on `comment_date`, but it does not. -1 – Andomar Jun 25 '11 at 14:24
comment_approved DOES do something. Did you try the query with / without? Without it will give you all the posts that have been commented even if they hadn't been approved. As for the date filter. To test it I put in YEAR instead of WEEK. As per in the question. I will edit it. – jackreichert Jun 25 '11 at 17:05

score 1 · Answer 3 · answered Jun 25 '11 at 09:53

1

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

answered Jun 25 '11 at 09:53

Andomar

232,371
49
380
404

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

3 Answers3