Query certain amount of posts from multiple dates

Question

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

MikeNGarrett · Accepted Answer · 2019-04-25 16:10:09Z

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 )

answered Apr 25, 2019 at 16:10

MikeNGarrett

2,6791 gold badge20 silver badges29 bronze badges

Thanks, I‘ll give it a shot when I get home. I‘ve noticed the last paragraph in my question was ambiguous, so I‘ve edited it.

idleberg
– idleberg

2019-04-25 16:27:37 +00:00
Commented Apr 25, 2019 at 16:27
Ah, you can't do that. You'll have to perform separate queries to get 2 posts per year.

MikeNGarrett
– MikeNGarrett

2019-04-25 16:42:20 +00:00
Commented Apr 25, 2019 at 16:42

Add a comment |

nmr · Accepted Answer · 2019-04-27 18:22:18Z

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);

Stack Exchange Network

Query certain amount of posts from multiple dates

2 Answers 2

Your Answer

Hot Network Questions

Query certain amount of posts from multiple dates

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions