从多个日期查询一定数量的帖子

时间:2019-04-25 作者:idleberg

我想知道是否可以使用一个查询来获取几年来的最后几篇帖子。

我目前正在使用foreach 循环运行多个查询,然后合并post数据。我的单个查询如下所示:

$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);
出于性能方面的原因,我假设最好使用一个达到相同效果的查询—如果可能的话。我正在努力实现的一个例子是从过去三年中的每一年中获得最后两个职位:

2016年两个职位,2017年两个职位,2018年两个职位

2 个回复
最合适的回答,由SO网友:MikeNGarrett 整理而成

你就在那里。每个date_query 数组表示要根据其进行筛选的单独条件。默认情况下,这些组合使用AND 这进一步限制了范围。在您的情况下,您希望将关系更改为OR 然后将不同的年份作为各自的独立条件添加到数组中。

$args  = array(
    \'date_query\' => array(
        \'relation\' => \'OR\',
        array(
            \'year\' => \'2018\',
        ),
        array(
            \'year\' => \'2015\',
        ),
    ),
);
生成的mysql语句如下所示:

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
重要的一点是( YEAR( wp_posts.post_date ) = 2018 OR YEAR( wp_posts.post_date ) = 2015 )

SO网友:nmr

可以在一个查询中获取数据,但需要在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);

相关推荐

使用新的WP-Query()从循环中过滤后期格式;

嗨,我目前正在为我的博客构建一个主题。下面的代码指向最新的帖子(特色帖子)。因为这将有一个不同的风格比所有其他职位。然而我想过滤掉帖子格式:链接使用我在循环中定义的WP查询,因为它给我带来了更多的灵活性。我该怎么做呢? <?php $featured = new WP_Query(); $featured->query(\'showposts=1\'); ?> <?php while ($featured->have_post