Most commented last 24h, week, month, year and all time – posts_where

1 Views

I’ve removed my old post here, as the posts_where method is outdated.

Thanks to kaiser, this is now how easy it is to list most commented posts within a certain time span:

Read More
<?php
$popular = new WP_Query( array(
    'post_type'             => array( 'post' ),
    'showposts'             => 6,
    'cat'                   => 'MyCategory',
    'ignore_sticky_posts'   => true,
    'orderby'               => 'comment_count',
    'order'                 => 'dsc',
    'date_query' => array(
        array(
            'after' => '1 week ago',
        ),
    ),
) );
?>
<?php while ( $popular->have_posts() ): $popular->the_post(); ?>
    <?php the_title(); ?>
<?php endwhile; ?>

Note: this sorts by most commented articles posted within X date, not checking all articles and then looking for most amount of comments within that time span among all articles. No idea how to do the second one, or how heavy it would be.

Post Views: 1

Related posts

1 comment

  1. Where?

    The WP_Comment_Query class has the following filter for its clauses:

    $pieces = array( 'fields', 'join', 'where', 'orderby', 'order', 'limits', 'groupby' );
$clauses = apply_filters_ref_array( 'comments_clauses', array( compact( $pieces ), &$this ) );

    That means you can hook into comments_clauses and filter the $clauses['where'] part.

    How?

    Since WP 3.7, we got a WP_Date_Query class. It works like the following (simplified examples):

    The default values that are used during WP_Query – retrieved via query vars:

    array(
    'year'     => '',
    'monthnum' => '',
    'week'     => '',
    'day'      => '',
    'hour'     => '',
    'minute'   => '',
    'second'   => '',
)

    How to do a date_query: Code example copied from here

    $dateQuery = new WP_Query( array(
    'date_query' => array(
        'column'   => 'optional, column to query against, default is post_date',
        'compare'  => 'optional, see WP_Date_Query::get_compare()',
        'relation' => 'optional, OR or AND, how the sub-arrays should be compared, default is AND',
        array(
            'column'    => 'see above',
            'compare'   => 'see above',
            'after'     => 'string or array, see WP_Date_Query::build_mysql_datetime()',
            'before'    => 'string or array, see WP_Date_Query::build_mysql_datetime()',
            'inclusive' => 'boolean, for after/before, whether exact value should be matched or not',
            'year'      => '4 digit int',
            'month'     => 'int, 1-12',
            'week'      => 'int, 0-53',
            'day'       => 'int, 1-31',
            'hour'      => 'int, 0-23',
            'minute'    => 'int, 0-60',
            'second'    => 'int, 0-60',
        ),
    ),
    // ... other query args
) );

    You can as well do a plain WP_Date_Query() and fetch the resulting SQL string for later use in clauses filters:

    $dateQuery = WP_Date_Query( array( /* your query */ ) );
$whereDate = $dateQuery->get_sql();

// Inspect result
var_dump( $whereDate );

    Now Same source a date query for a post and its comments:

    // All comments that are within the past week
$some_comments = get_comments( array(
    'post_ID'    => get_the_ID(), // Can be omited as well
    'date_query' => array(
        array(
            'after' => '1 week ago',
        ),
    ),
    'count' => true // return only the comment count
) );

    Mocked up

    Not tested …

    global $wp_query;
$daysQuery = new WP_Query( array(
    'post_type'           => array( 'post' ),
    'showposts'           => 6,
    'cat'                 => 'mycat',
    'ignore_sticky_posts' => true,
    'orderby'             => 'comment_count date',
    'date_query' => array(
        array(
            'after'  => array(
                'year'  => date( "Y" ),
                'month' => date( "m" ),
                'day'   => "01",
            ),
            'before' => array(
                'year'  => date( "Y" ),
                'month' => date( "m", strtotime( "-1 Months" ) ),
                'day'   => date( "t", strtotime( "-1 Months" ) ),
            ),
            // 'inclusive' => true,
            'month'     => date( "m" ),
        ),
    ),
) );

Comments are closed.