Support » Plugins » Custom Query With Join and Date Range

  • Resolved rpophessagr

    (@rpophessagr)


    I’m trying to do a query 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, count( comment_post_id )
    	 FROM $wpdb->posts wposts, $wpdb->comments wcomments
    	 WHERE wposts.ID = wcomments.comment_post_ID
    	 AND wcomments.comment_date  > ".date( 'Y-m-d', strtotime('-7 days')."
    	 ORDER BY comment_count DESC
    	 LIMIT 0 ,  10
     ";
    
     $pageposts = $wpdb->get_results($querystr);

    Thanks in advance.

Viewing 1 replies (of 1 total)
  • 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
     ";
Viewing 1 replies (of 1 total)
  • The topic ‘Custom Query With Join and Date Range’ is closed to new replies.