I'm using the following MySQL statement to grab the topics (BBpress custom post type) that have the most replies (custom post type like comments) in the last X days.

However I've found that I get more topics returned when I change $duration to '-7 days' than when I use '-30 days', which makes no sense.

What am I doing wrong?

// Set how far back I want to look
$period = date('Y-m-d', strtotime('-30 days'));

// Grab all replies in the last 30 days and group them by the topic they are attached to
$query = $wpdb->prepare("
    SELECT post_parent, count( post_parent ) AS c, SUBSTRING( post_date, 1, 10 ) AS d
    FROM        $wpdb->posts
    WHERE       (post_date >= %s) AND (post_type = 'reply')
    GROUP BY post_parent
    ", $period);

// Put the return topic ID's into an array for use with WP_Query
$col_ids = $wpdb->get_col($query);
$postids = implode($col_ids,', ');

$args = array(
    'post__in' => $col_ids,
    'post_type' => 'topic',
    'posts_per_page' => 7
  • Grouping by post_parent is going to truncate your results. Only one result of the "group" will show up. Thus, by chance, you may have more in 7 days than in 30, depending on how the grouping works.
    – s_ha_dum
    Oct 24, 2015 at 20:22
  • @s_ha_dum - That's intentional. The replies are being grouped by the ID of their parent topic in order to return the topic ID's. There should still be more groups returned by 30 days than 7 days. Unless I'm missing something?
    – jetlej
    Oct 25, 2015 at 15:18
  • Run the query without the grouping and parse (count) the results in PHP. Do you still see the same weird counts?
    – s_ha_dum
    Oct 25, 2015 at 15:54


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.