Query certain amount of posts from multiple dates

admin管理员组

文章数量:1430648

I was wondering whether it's possible to use one query to get the last couple of posts from several years.

I'm currently using a foreach loop to run several queries and then merge the post data. My single query looks like this:

$amount = 2;
$year = 2018;

$args = array(
    'post_type'         => 'post',
    'post_status'       => 'publish',
    'posts_per_page'    => $amount,
    'date_query'        => array(
        array(
            'year' => $year
        )
    )
);

$query = new WP_Query($args);

For performance reasons, I'm assuming it's better to use one query that achieves the same – if that's even possible. An example of what I'm trying to achieve is get the last two posts from each of the last three years:

• two posts from 2016
• two posts from 2017
• two posts from 2018

I was wondering whether it's possible to use one query to get the last couple of posts from several years.

I'm currently using a foreach loop to run several queries and then merge the post data. My single query looks like this:

$amount = 2;
$year = 2018;

$args = array(
    'post_type'         => 'post',
    'post_status'       => 'publish',
    'posts_per_page'    => $amount,
    'date_query'        => array(
        array(
            'year' => $year
        )
    )
);

$query = new WP_Query($args);

For performance reasons, I'm assuming it's better to use one query that achieves the same – if that's even possible. An example of what I'm trying to achieve is get the last two posts from each of the last three years:

• two posts from 2016
• two posts from 2017
• two posts from 2018

• wp-query

Share Improve this question edited Apr 25, 2019 at 16:23 idleberg asked Apr 25, 2019 at 14:54 idlebergidleberg 15766 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 3

You're just about there. Each date_query array represents a separate condition to filter by. By default, these are combined using AND which limits the scope further. In your case, you want to change the relation to OR and then add the different years as their own separate conditions in the array.

$args  = array(
    'date_query' => array(
        'relation' => 'OR',
        array(
            'year' => '2018',
        ),
        array(
            'year' => '2015',
        ),
    ),
);

The resulting mysql statement looks like this:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND ( YEAR( wp_posts.post_date ) = 2018 OR YEAR( wp_posts.post_date ) = 2015 ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date DESC LIMIT 0, 10

The important bit is ( YEAR( wp_posts.post_date ) = 2018 OR YEAR( wp_posts.post_date ) = 2015 )

Getting data in one query is possible, but it will require the use of session variables in the SQL and several filters to modify WordPress query.

Target query looks roughly like this:

SELECT * FROM ( 
    /* 
    WP query with additional fields in select, '_inner_rank', '_assign_current'
    */ 
) ranked WHERE ranked._inner_rank <= 2;

And this is the key fragment added in the posts_fields filter hook:

@vrank := IF(@cyear = year(post_date), @vrank+1, 1) as _inner_rank, @cyear := year(post_date) as _assign_current

Variable @cyear was used to "divide" the results into groups, and @vrank to number the found rows.
The data (posts) are sorted by date, the variable cyear stores the year from the previous row and is compared with the value (year from post_date) from the current row. If the values differ, the numbering starts again, if they match - the numbering continues. After comparison, the value from the current row is assigned to the variable cyear.

Code

add_filter('posts_fields',      'se336295_fields__custom_select', 30, 2);
add_filter('posts_join_paged',  'se336295_join__custom_select', 30, 2);
add_filter('posts_orderby',     'se336295_orderby_custom_select', 30, 2);
add_filter('posts_request',     'se336295_request__custom_select', 30, 2);
add_filter('split_the_query',   'se336295_split__custom_select', 30, 2);

function se336295_split__custom_select( $split_the_query, $query )
{
    if ( ! $query->get('get_from_group', false) )
        return $split_the_query;

    return false;
}
/**
 * @param string   $fields The SELECT clause of the query.
 * @param WP_Query $this   The WP_Query instance (passed by reference).
 */
function se336295_fields__custom_select( $fields, $query ) 
{
    global $wpdb;
    if ( ! $query->get('get_from_group', false) )
        return $fields;

    $table = $wpdb->posts;
    $fields .= ", @vrank := IF(@cyear = year({$table}.post_date), @vrank+1, 1) as _inner_rank, @cyear := year({$table}.post_date) as _assign_current ";

    return $fields;
}
/**
 * @param string   $join  The JOIN clause of the query.
 * @param WP_Query $this The WP_Query instance (passed by reference).
 */
function se336295_join__custom_select( $join, $query ) 
{ 
    if ( ! $query->get('get_from_group', false) )
        return $join;

    $join .= ", (select @cyear := 0, @vrank := 0) tmp_iv ";
    return $join; 
}
/**
 * @param string   $orderby The ORDER BY clause of the query.
 * @param WP_Query $this The WP_Query instance (passed by reference).
 */
function se336295_orderby_custom_select( $orderby, $query ) 
{ 
    $count = $query->get('get_from_group', false);
    if ( ! $count )
        return $orderby;

    //
    // close main statement (outer SELECT)
    $orderby .= ") ranked ";
    if ( is_numeric($count) && $count > 0)
        $orderby .= sprintf("WHERE ranked._inner_rank <= %d", $count);

    return $orderby; 
}
/*
 * @param string   $request The complete SQL query.
 * @param WP_Query $this    The WP_Query instance (passed by reference).
 */
function se336295_request__custom_select( $request, $query ) 
{
    if ( ! $query->get('get_from_group', false) )
        return $request;

    //
    // start main statement (outer SELECT)
    $i = stripos( $request, 'SQL_CALC_FOUND_ROWS');
    if ( $i === FALSE )
        $request = "SELECT * FROM (" . $request;
    else
        $request = "SELECT SQL_CALC_FOUND_ROWS  * FROM ( SELECT " . substr( $request, $i+20 );

    return $request;
}

How to use

$arg = [
    'get_from_group' => 2,   // <-- number of posts from each group
    'post_type'      =>'post',
    'date_query' => [
        'after'  => [ 'year' => '2016',],
        'before' => [ 'year' => '2018',],
        'inclusive' => true,
    ],
    //'posts_per_page' => 20,
    //'paged' => $curr_page,   // optional pagination
    //'orderby' => 'date',     // default value
];
$qr1 = new WP_Query($arg);

本文标签： Query certain amount of posts from multiple dates