可以在一个查询中获取数据,但需要在SQL中使用会话变量和几个过滤器来修改WordPress查询。
目标查询大致如下所示:
SELECT * FROM (
/*
WP query with additional fields in select, \'_inner_rank\', \'_assign_current\'
*/
) ranked WHERE ranked._inner_rank <= 2;
这是在
posts_fields
过滤器挂钩:
@vrank := IF(@cyear = year(post_date), @vrank+1, 1) as _inner_rank, @cyear := year(post_date) as _assign_current
变量
@cyear
用于将结果“分成”组
@vrank
为找到的行编号
数据(帖子)按日期、变量
cyear
存储上一行中的年份,并与值(年份自
post_date
) 从当前行。如果值不同,则重新开始编号,如果匹配,则继续编号。比较后,将当前行中的值分配给变量
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);