文章/答案/技术大牛

发布

社区首页 >问答首页 >Wordpress组合查询

问Wordpress组合查询
EN

Stack Overflow用户

提问于 2014-05-09 01:05:07

回答 3查看 7K关注 0票数 5

我有两个问题，我需要尝试合并，以便我的分页正确工作，并以正确的顺序显示帖子。

我有一个疑问：

$today = date('m/d/Y', strtotime('today'));

$args = array(
    'post_type' => 'workshops',
    "posts_per_page" => 5,
    "paged" => $paged,
    'meta_key' => 'select_dates_0_workshop_date',
    'orderby' => 'meta_value',
    'order' => 'ASC',
        'meta_query' => array(
            array(
                  'key' => 'select_dates_0_workshop_date',
                  'meta-value' => "meta_value",
                  'value' => $today,
                  'compare' => '>=',
                  'type' => 'CHAR'
             )
    )
    );

这个查询的结果需要在上面的查询之后：

$args = array(
    'post_type' => 'workshops',
    "posts_per_page" => 5,
    "paged" => $paged,
    'meta_key' => 'select_dates_0_workshop_date',
    'orderby' => 'meta_value',
    'order' => 'DESC',
        'meta_query' => array(
            array(
                 'key' => 'select_dates_0_workshop_date',
                 'meta-value' => "meta_value",
                 'value' => $today,
                 'compare' => '<',
                 'type' => 'CHAR'
        )
    )
    );

这两个查询之间的区别是：'order'和'compare'。

我已经在纯MYSQL查询中这样做了，但是我不知道如何在WordPress上这样做。

php

mysql

post

wordpress

回答 3

Stack Overflow用户

回答已采纳

发布于 2014-05-16 20:32:36

这是一个更新版本的答案，这比前一个更加灵活。

这里有一个使用SQL UNION的想法

我们可以使用来自posts_clauses过滤器的数据来重写来自posts_request筛选器的posts_request查询。
我们扩展了WP_Query类以实现我们的目标。我们实际上做了两次：

- `WP_Query_Empty`: to get the generated SQL query of each sub-queries, but without doing the database query.
- `WP_Query_Combine`: to fetch the posts.

以下实现支持组合N子查询.

以下是两个演示：

演示1：

假设您有6个按日期(DESC)排序的帖子：

CCC
AAA
BBB
CCC
YYY
ZZZ
XXX

其中XXX、YYY和ZZZ都比DT=2013-12-14 13:03:40老。

让我们订购我们的帖子，以便在DT之后发布的文章按标题订购，在DT之前发布的帖子按标题订购：

AAA
BBB
CCC
ZZZ
YYY
XXX

然后我们可以使用以下方法：

/**
 * Demo #1 - Combine two sub queries:
 */

$args1 = array(
    'post_type'  => 'post',
    'orderby'    => 'title',
    'order'      => 'ASC',
    'date_query' => array(
        array( 'after' => '2013-12-14 13:03:40' ),
    ),
);

$args2 = array(
    'post_type'  => 'post',
    'orderby'    => 'title',
    'order'      => 'DESC',
    'date_query' => array(
        array( 'before' => '2013-12-14 13:03:40', 'inclusive' => TRUE ),    
    ),
);

$args = array( 
   'posts_per_page' => 1,
   'paged'          => 1,
   'sublimit'       => 1000,
   'args'           => array( $args1, $args2 ),
);

$results = new WP_Combine_Queries( $args );

这将生成以下SQL查询：

SELECT SQL_CALC_FOUND_ROWS * FROM ( 
    ( SELECT wp_posts.* 
        FROM wp_posts 
        WHERE 1=1 
            AND ( ( post_date > '2013-12-14 13:03:40' ) ) 
            AND wp_posts.post_type = 'post' 
            AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
            ORDER BY wp_posts.post_title ASC 
            LIMIT 1000
    ) 
    UNION 
    ( SELECT wp_posts.* 
        FROM wp_posts 
        WHERE 1=1 
        AND ( ( post_date <= '2013-12-14 13:03:40' ) ) 
        AND wp_posts.post_type = 'post' 
        AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
        ORDER BY wp_posts.post_title DESC 
        LIMIT 1000
    ) 
) as combined LIMIT 0, 10

演示2：

下面是你的例子：

/**
 * Demo #2 - Combine two sub queries:
 */

$today = date( 'm/d/Y', strtotime( 'today' ) );

$args1 = array(
    'post_type'      => 'workshops',
    'meta_key'       => 'select_dates_0_workshop_date',
    'orderby'        => 'meta_value',
    'order'          => 'ASC',
    'meta_query'     => array(
        array(
            'key'         => 'select_dates_0_workshop_date',
            'value'       => $today,
            'compare'     => '>=',
            'type'        => 'CHAR',
        ),
    )
);

$args2 = array(
    'post_type'      => 'workshops',
    'meta_key'       => 'select_dates_0_workshop_date',
    'orderby'        => 'meta_value',
    'order'          => 'DESC',
    'meta_query'     => array(
        array(
            'key'         => 'select_dates_0_workshop_date',
            'value'       => $today,
            'compare'     => '<',
            'type'        => 'CHAR',
        ),
    )
);

$args = array( 
   'posts_per_page' => 5,
   'paged'          => 4,
   'sublimit'       => 1000,
   'args'           => array( $args1, $args2 ),
);

$results = new WP_Combine_Queries( $args );

这将为您提供一个类似于此的查询：

SELECT SQL_CALC_FOUND_ROWS * FROM ( 
    ( SELECT wp_posts.* 
        FROM wp_posts 
        INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
        INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
        WHERE 1=1 
            AND wp_posts.post_type = 'workshops' 
            AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') 
            AND (wp_postmeta.meta_key = 'select_dates_0_workshop_date' AND (mt1.meta_key = 'select_dates_0_workshop_date' AND CAST(mt1.meta_value AS CHAR) >= '05/16/2014') ) 
            GROUP BY wp_posts.ID 
            ORDER BY wp_postmeta.meta_value ASC
            LIMIT 1000 
        ) 
    UNION 
    ( SELECT wp_posts.* 
        FROM wp_posts 
        INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
        INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
        WHERE 1=1 
            AND wp_posts.post_type = 'workshops' 
            AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') 
            AND (wp_postmeta.meta_key = 'select_dates_0_workshop_date' AND (mt1.meta_key = 'select_dates_0_workshop_date' AND CAST(mt1.meta_value AS CHAR) < '05/16/2014') ) 
            GROUP BY wp_posts.ID 
            ORDER BY wp_postmeta.meta_value DESC 
            LIMIT 1000 
        ) 
) as combined LIMIT 15, 5

演示3：

我们还可以合并两个以上的子查询：

/**
 * Demo #3 - Combine four sub queries:
 */

$args = array( 
   'posts_per_page' => 10,
   'paged'          => 1,
   'sublimit'       => 1000,
   'args'           => array( $args1, $args2, $args3, $args4 ),
);

$results = new WP_Combine_Queries( $args );

课程：

下面是我们的演示课程：

/**
 * Class WP_Combine_Queries
 * 
 * @uses WP_Query_Empty
 * @link https://stackoverflow.com/a/23704088/2078474
 *
 */

class WP_Combine_Queries extends WP_Query 
{
    protected $args    = array();
    protected $sub_sql = array();
    protected $sql     = '';

    public function __construct( $args = array() )
    {
        $defaults = array(
            'sublimit'       => 1000,
            'posts_per_page' => 10,
            'paged'          => 1,
            'args'           => array(),
        );

        $this->args = wp_parse_args( $args, $defaults );

        add_filter( 'posts_request',  array( $this, 'posts_request' ), PHP_INT_MAX  );

        parent::__construct( array( 'post_type' => 'post' ) );
    }

    public function posts_request( $request )
    {
        remove_filter( current_filter(), array( $this, __FUNCTION__ ), PHP_INT_MAX  );

        // Collect the generated SQL for each sub-query:
        foreach( (array) $this->args['args'] as $a )
        {
            $q = new WP_Query_Empty( $a, $this->args['sublimit'] );
            $this->sub_sql[] = $q->get_sql();
            unset( $q );
        }

        // Combine all the sub-queries into a single SQL query.
        // We must have at least two subqueries:
        if ( count( $this->sub_sql ) > 1 )
        {
            $s = '(' . join( ') UNION (', $this->sub_sql ) . ' ) ';

            $request = sprintf( "SELECT SQL_CALC_FOUND_ROWS * FROM ( $s ) as combined LIMIT %s,%s",
                $this->args['posts_per_page'] * ( $this->args['paged']-1 ),
                $this->args['posts_per_page']
            );          
        }
        return $request;
    }

} // end class

/**
 * Class WP_Query_Empty
 *
 * @link https://stackoverflow.com/a/23704088/2078474
 */

class WP_Query_Empty extends WP_Query 
{
    protected $args      = array();
    protected $sql       = '';
    protected $limits    = '';
    protected $sublimit  = 0;

    public function __construct( $args = array(), $sublimit = 1000 )
    {
        $this->args     = $args;
        $this->sublimit = $sublimit;

        add_filter( 'posts_clauses',  array( $this, 'posts_clauses' ), PHP_INT_MAX  );
        add_filter( 'posts_request',  array( $this, 'posts_request' ), PHP_INT_MAX  );

        parent::__construct( $args );
    }

    public function posts_request( $request )
    {
        remove_filter( current_filter(), array( $this, __FUNCTION__ ), PHP_INT_MAX );
        $this->sql = $this->modify( $request );             
        return '';
    }

    public function posts_clauses( $clauses )
    {
        remove_filter( current_filter(), array( $this, __FUNCTION__ ), PHP_INT_MAX  );
        $this->limits = $clauses['limits'];
        return $clauses;
    }

    protected function modify( $request )
    {
        $request = str_ireplace( 'SQL_CALC_FOUND_ROWS', '', $request );

        if( $this->sublimit > 0 )
            return str_ireplace( $this->limits, sprintf( 'LIMIT %d', $this->sublimit ), $request );
        else
            return $request;
    }

   public function get_sql( )
    {
        return $this->sql;
    }

} // end class

然后，您可以根据需要调整这些类。

我使用技巧这里提到的来保持UNION子查询的顺序。您可以使用我们的sublimit参数相应地修改它。

这也适用于主查询，例如使用posts_request过滤器。

我希望这能帮到你。

票数 10

Stack Overflow用户

发布于 2014-05-16 20:26:55

问题在于'order‘论点：'meta_query’论点接受元查询的数组。以及'relation‘论点(基本上是“和”或" or ")。这可以用来选择$today少于或大于或等于的帖子(尽管在那时，元查询是无用的，因为它选择的是每一个(" or ")或没有(“和”)工作坊。不幸的是，WP_Query不允许您任意排序结果(从最近即将到来的研讨会切换到最近的研讨会)。

如果您知道如何在MySQL中直接实现这一点，您可能会考虑查看查询过滤器，特别是过滤器。这些过滤器使您能够开始处理由WP_Query类生成的实际SQL，而不必切换到完全自定义的查询。但是，请预先警告，限制这些过滤器的范围或将它们应用于站点上的每个查询是很重要的( 过滤文档有一些关于如何这样做的好例子)。

票数 3

Stack Overflow用户

发布于 2022-01-11 22:29:25

$now = gmdate( 'Y-m-d H:i:s' );

$u_webinars = new WP_Query( array(
    'fields'                => 'ids',
    'post_type'             => 'webinars',
    'meta_key'              => 'start_end_date_%_xtk_start_date',
    'posts_per_page'        => -1,
    'orderby'               => 'meta_value',
    'order'                 => 'ASC',
    'meta_query' => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
        array(
            'key'       => 'start_end_date_%_xtk_end_date',
            'compare'   => '>',
            'value'     => $now,
            'type'      => 'DATETIME'
        )
    )
) );

$p_webinars = new WP_Query( array(
    'fields'            => 'ids',
    'post_type'         => 'webinars',
    'meta_key'          => 'start_end_date_%_xtk_start_date',
    'posts_per_page'    => -1,
    'orderby'           => 'meta_value',
    'order'             => 'DESC',
    'meta_query' => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
        array(
            'key'       => 'start_end_date_%_xtk_end_date',
            'compare'   => '<',
            'value'     => $now,
            'type'      => 'DATETIME'
        )
    )
) );

$all_ids_arr = array_merge( $u_webinars->posts, $p_webinars->posts );

$page_number = ( get_query_var( 'paged' ) ) ? get_query_var( 'paged' ) : 1;
$all_webinars = new WP_Query( array( 
        'post__in'          => $all_ids_arr, 
        'posts_per_page'    => 16,
        'paged'             => $page_number,
        'post_type'         => 'webinars',
        'orderby'           => 'post__in'
    ) 
); 

// Loop will be written here

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/23555109

复制

相似问题

问Wordpress组合查询
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Wordpress组合查询EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Wordpress组合查询
EN