WordPress结合查询 [英] Wordpress combine queries
问题描述
我有两个查询需要尝试合并，以便我的分页可以正常工作，并且帖子以正确的顺序显示.
I have two queries that I need to try to combine so that my pagination works correctly, and the posts show up in the correct order.
我有一个查询:
$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'
)
)
);
此查询的结果必须在上面的查询之后:
The results of this query need to come after the query above:
$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'.
The difference between the two queries is the: 'order' and the 'compare'.
我已经在纯MYSQL查询中完成了此操作，但是我不确定如何在WordPress上执行此操作
I have done this in pure MYSQL Queries, but I am not sure how to do this on WordPress
推荐答案
这是答案的更新版本，比上一个版本更灵活.
这是使用SQL UNION的一个主意:
Here's one idea using a SQL UNION:
-
我们可以使用
posts_clauses过滤器中的数据来重写posts_request过滤器中的SQL查询.
We can use the data from the
posts_clausesfilter to rewrite the SQL query from theposts_requestfilter.
我们扩展了WP_Query类以实现我们的目标.实际上，我们做了两次:
We extend the WP_Query class to achieve our goal. We actually do that twice:
-
WP_Query_Empty:获取每个子查询的生成的SQL查询，但不执行数据库查询. -
WP_Query_Combine:获取帖子.
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子查询.
The following implementation supports combining N sub-queries.
这是两个演示:
假设您有六个帖子，按日期(DESC)排序:
Let's assume you have six posts, ordered by date (DESC):
CCC
AAA
BBB
CCC
YYY
ZZZ
XXX
其中XXX，YYY和ZZZ早于DT=2013-12-14 13:03:40.
让我们对帖子进行排序，以使DT之后发布的帖子按标题(ASC)排序，而DT之前发布的发布按标题(DESC)排序:
Let's order our posts so that posts published after DT are ordered by title (ASC) and posts publisehd before DT are ordered by title (DESC):
AAA
BBB
CCC
ZZZ
YYY
XXX
然后我们可以使用以下内容:
Then we can use the following:
/**
* 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查询:
This generates the following SQL query:
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:
Here's your example:
/**
* 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 );
这应该给您这样的查询:
This should give you a query like this one:
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:
We could also combine more than two sub queries:
/**
* 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 );
课程:
这是我们的演示课程:
Classes:
Here are our demo classes:
/**
* 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
然后您可以根据需要调整班级.
You can then adjust the classes to your needs.
我使用此处提到的技巧 来保留UNION子查询的顺序.
您可以使用我们的sublimit参数进行相应的修改.
I use the trick mentioned here to preserve the order of UNION sub queries.
You can modify it accordingly with our sublimit parameter.
例如，通过使用posts_request过滤器，此方法也应适用于主要查询.
This should also work for main queries, by using the posts_request filter, for example.
我希望这会有所帮助.
这篇关于WordPress结合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
