Query Posts By Post Publish Date, but sort by Custom Meta Key

Question

I'm trying to show the most popular (recent) posts on my blog. I have made a special formula for this. I am querying for these posts like so:

$args = array(
'post_type'     => 'post',
'post_status'   => 'publish',
'meta_key'      => '_post_popularity',
'meta_query'  => array(
    array(
        'key' => 'post_date',
        'value' => date("Y-m-d H:i:s", strtotime('-30 days')),
        'compare' => '>',
        'type' => 'DATETIME'
    )
),
'orderby'       => 'meta_value_num',
'order'         => 'DSC');
query_posts( $args ); 

I don't think that is correct however, as WP is interpreting "key" as a meta_key, whereas I want to use the Publish Date in the post table.

Taking out "meta_query" works fine, I can see the most popular posts, but it doesn't restrict it to the most recent ones. I check the documentation but post_date is not mentioned as a way to "filter", only sort.

Is this possible? Thanks.

Answer 1

As you've discovered, meta queries only work with the post meta table, it is indeed looking for a meta key of post_date.

You can't do timespan queries with a simple query, but we can accomplish this with a combination of a query for the meta key, and a posts_where filter directly on the SQL to get the time span.

first, the filter function (from WP_Query):

function wpa57065_filter_where( $where = '' ) {
    // posts in the last 30 days
    $where .= " AND post_date > '" . date('Y-m-d', strtotime('-30 days')) . "'";
    return $where;
}

then the query, adding then removing the filter immediately after:

$args = array(
    'meta_key' => '_post_popularity',
    'orderby' => 'meta_value_num',
    'order' => 'DSC'
);

add_filter( 'posts_where', 'wpa57065_filter_where' );
$recent_popular = new WP_Query( $args );
remove_filter( 'posts_where', 'wpa57065_filter_where' );

if( $recent_popular->have_posts() ):
    while( $recent_popular->have_posts() ):
        $recent_popular->the_post();
        // your loop stuff
    endwhile;
endif;