问题描述
我正在编写一个插件，使搜索仅在特定的自定义帖子类型上工作，并根据用户在其后端配置中选择的分类法对结果进行排序。
管理员可以选择最多 4 个自定义帖子类型的分类：每个都是必须应用于搜索结果的排序标准。然后，管理员可以在搜索结果中选择使其他人的帖子显示在每个分类法中的条款。第一选择的分类法是最重要的分类标准; 其他将按顺序应用。
我需要一个查询：
SELECT * FROM wp_posts ORDER BY choosen_terms_of_taxonomy1, choosen_terms_of_axonomy2, ...
现在我们来使用一些样本数据。我们有 course 帖子类型：
+----+------------+
| ID | post_title |
+----+------------+
| 12 | Cooking |
+----+------------+
| 13 | Surfing |
+----+------------+
| 14 | Building |
+----+------------+
| 15 | Hacking |
+----+------------+
那么我们有两个这样的自定义帖子类型的分类。一个是 place，另一个是 pricetag 。 place 分类法有以下术语：
+---------+------------+
| term_id | slug |
+---------+------------+
| 21 | downtown |
+---------+------------+
| 22 | abroad |
+---------+------------+
pricetag 分类法具有以下术语：
+---------+------------+
| term_id | slug |
+---------+------------+
| 31 | expensive |
+---------+------------+
| 32 | cheap |
+---------+------------+
最后我们有 wp_term_relationships，通过这种方式将课程链接到分类术语：
+-----------+------------+---------+
| object_id | post_title | term_id |
+-----------+------------+---------+
| 12 | Cooking | 21 | (downtown)
+-----------+------------+---------+
| 12 | Cooking | 32 | (cheap)
+-----------+------------+---------+
| 13 | Surfing | 22 | (abroad)
+-----------+------------+---------+
| 13 | Surfing | 31 | (expensive)
+-----------+------------+---------+
| 14 | Building | 21 | (downtown)
+-----------+------------+---------+
| 14 | Building | 31 | (expensive)
+-----------+------------+---------+
| 15 | Hacking | 22 | (abroad)
+-----------+------------+---------+
| 15 | Hacking | 32 | (cheap)
+-----------+------------+---------+
(请注意：我知道这不是 wp_term_relationships 表的真实结构，但是为了简化这个问题，我想简化一下。)
我们假设管理员已经选择了 place 作为第一个分类标准，作为一个排序标准，他选择首先显示 downtown 课程 (该插件的管理屏幕已经完成，它已经向管理员提供了 UI 进行这样的选择) 。
然后说管理员已经选择 pricetag 作为第二个分类用作分类标准，他希望 expensive 课程首先显示。请注意，作为第二个标准，排序优先级低于第一个标准，因此管理员首先需要市中心课程，然后在市中心课程组中首先选择昂贵的课程。
现在，前端用户搜索网站上的所有课程，他应该按照这个顺序看到这些结果：
-
建筑课程 (因为它是市中心和昂贵的)
-
烹饪课程 (因为它是市中心和便宜)
-
冲浪课程 (因为它在国外和昂贵)
-
黑客课程 (因为它在国外和便宜)
我的问题是在 Wordpress 查询对象中编写正确的 JOIN 和 ORDER BY 子句。我已经挂接了 posts_clauses，这里是我生成的 SQL 查询：
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts
LEFT JOIN (wp_term_relationships, wp_term_taxonomy, wp_terms)
ON (wp_term_relationships.object_id = wp_posts.ID
AND wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
AND wp_terms.term_id = wp_term_taxonomy.term_id)
WHERE 1=1 AND (((wp_posts.post_title LIKE '%%') OR (wp_posts.post_excerpt LIKE '%%') OR (wp_posts.post_content LIKE '%%')))
AND wp_posts.post_type IN
('post', 'page', 'attachment', 'course')
AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private')
AND wp_posts.post_type='course'
ORDER BY (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') DESC,
(wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') DESC,
(wp_terms.slug LIKE 'expensive' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') DESC,
(wp_terms.slug LIKE 'cheap' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') DESC,
wp_posts.post_title LIKE '%%' DESC, wp_posts.post_date DESC
LIMIT 0, 300
但这个查询至少有两个问题：
-
它返回两倍的结果，我不明白为什么，因为 LEFT JOIN 不应该在指定的表之间产生笛卡尔乘积
-
结果排序顺序不清楚 (似乎只是
post_date DESC)，但很明显，这不是我期待的。
我尝试通过删除 Wordpress 生成的子句来简化查询：
SELECT wp_posts.* FROM wp_posts
LEFT JOIN (wp_term_relationships, wp_term_taxonomy, wp_terms)
ON (wp_term_relationships.object_id = wp_posts.ID
AND wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
AND wp_terms.term_id = wp_term_taxonomy.term_id)
WHERE 1=1 AND wp_posts.post_type='course'
ORDER BY (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') DESC,
(wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') DESC,
(wp_terms.slug LIKE 'expensive' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') DESC,
(wp_terms.slug LIKE 'cheap' AND wp_term_taxonomy.taxonomy LIKE 'pricetag') DESC
这一个有完全相同的问题，但它有点容易理解，它返回数据，就好像没有 ORDER BY 一样。
你能帮我吗？
最佳解决方案
不幸的是，虽然 WP_Query 支持'tax_query'参数，但它不支持基于后期的排序。因此，您将需要像现在所做的那样修改查询 SQL 。但是，您正在错误地构建 ORDER BY 子句，这就是为什么由 post_date 进行排序。您需要做的是使用 CASE 语句，如下所示：
CASE
WHEN (wp_terms.slug LIKE 'downtown' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 1
WHEN (wp_terms.slug LIKE 'abroad' AND wp_term_taxonomy.taxonomy LIKE 'place') THEN 0
END
这将根据您分配给每个术语的优先级 (1，0 等)，更高的优先级，除非您使用 ASC 而不是 DESC 进行排序。
因为你想独立订购这两个分类，你需要有两个连接和两个 case 语句。 (例如见下文)
您还需要在帖子 ID 上导出 GROUP BY，以避免重复的结果：
$clauses['groupby'] = 'wptests_posts.ID';
所以你的最终查询最终会看起来像这样 (格式化以便于阅读)：
SELECT SQL_CALC_FOUND_ROWS wptests_posts.ID FROM wptests_posts
LEFT JOIN (
wptests_term_relationships tr_place,
wptests_term_taxonomy tt_place,
wptests_terms t_place
) ON (
tr_place.object_id = wptests_posts.ID
AND tt_place.term_taxonomy_id = tr_place.term_taxonomy_id
AND tt_place.taxonomy = 'place'
AND t_place.term_id = tt_place.term_id
)
LEFT JOIN (
wptests_term_relationships tr_pricetag,
wptests_term_taxonomy tt_pricetag,
wptests_terms t_pricetag
) ON (
tr_pricetag.object_id = wptests_posts.ID
AND tt_pricetag.term_taxonomy_id = tr_pricetag.term_taxonomy_id
AND tt_pricetag.taxonomy = 'pricetag'
AND t_pricetag.term_id = tt_pricetag.term_id
)
WHERE 1=1 AND wptests_posts.post_type = 'course' AND (wptests_posts.post_status = 'publish')
GROUP BY wptests_posts.ID
ORDER BY
(CASE
WHEN (t_place.slug LIKE 'downtown') THEN 1
WHEN (t_place.slug LIKE 'abroad') THEN 0
END) DESC, (CASE
WHEN (t_pricetag.slug LIKE 'expensive') THEN 1
WHEN (t_pricetag.slug LIKE 'cheap') THEN 0
END) DESC,
wptests_posts.post_date DESC
LIMIT 0, 10
这是一个示例 PHPUnit 测试，演示了这个工作，包括生成连接和 orderbys 的示例代码 (用于生成上面的查询)：
class My_Test extends WP_UnitTestCase {
public function test() {
// Create the post type.
register_post_type( 'course' );
// Create the posts.
$cooking_post_id = $this->factory->post->create(
array( 'post_title' => 'Cooking', 'post_type' => 'course' )
);
$surfing_post_id = $this->factory->post->create(
array( 'post_title' => 'Surfing', 'post_type' => 'course' )
);
$building_post_id = $this->factory->post->create(
array( 'post_title' => 'Building', 'post_type' => 'course' )
);
$hacking_post_id = $this->factory->post->create(
array( 'post_title' => 'Hacking', 'post_type' => 'course' )
);
// Create the taxonomies.
register_taxonomy( 'place', 'course' );
register_taxonomy( 'pricetag', 'course' );
// Create the terms.
$downtown_term_id = wp_create_term( 'downtown', 'place' );
$abroad_term_id = wp_create_term( 'abroad', 'place' );
$expensive_term_id = wp_create_term( 'expensive', 'pricetag' );
$cheap_term_id = wp_create_term( 'cheap', 'pricetag' );
// Give the terms to the correct posts.
wp_add_object_terms( $cooking_post_id, $downtown_term_id, 'place' );
wp_add_object_terms( $cooking_post_id, $cheap_term_id, 'pricetag' );
wp_add_object_terms( $surfing_post_id, $abroad_term_id, 'place' );
wp_add_object_terms( $surfing_post_id, $expensive_term_id, 'pricetag' );
wp_add_object_terms( $building_post_id, $downtown_term_id, 'place' );
wp_add_object_terms( $building_post_id, $expensive_term_id, 'pricetag' );
wp_add_object_terms( $hacking_post_id, $abroad_term_id, 'place' );
wp_add_object_terms( $hacking_post_id, $cheap_term_id, 'pricetag' );
$query = new WP_Query(
array(
'fields' => 'ids',
'post_type' => 'course',
)
);
add_filter( 'posts_clauses', array( $this, 'filter_post_clauses' ) );
$results = $query->get_posts();
$this->assertSame(
array(
$building_post_id,
$cooking_post_id,
$surfing_post_id,
$hacking_post_id,
)
, $results
);
}
public function filter_post_clauses( $clauses ) {
global $wpdb;
$clauses['orderby'] = "
(CASE
WHEN (t_place.slug LIKE 'downtown') THEN 1
WHEN (t_place.slug LIKE 'abroad') THEN 0
END) DESC, (CASE
WHEN (t_pricetag.slug LIKE 'expensive') THEN 1
WHEN (t_pricetag.slug LIKE 'cheap') THEN 0
END) DESC,
" . $clauses['orderby'];
foreach ( array( 'place', 'pricetag' ) as $taxonomy ) {
// Instead of interpolating directly here, you should use $wpdb->prepare() for $taxonomy.
$clauses['join'] .= "
LEFT JOIN (
$wpdb->term_relationships tr_$taxonomy,
$wpdb->term_taxonomy tt_$taxonomy,
$wpdb->terms t_$taxonomy
) ON (
tr_$taxonomy.object_id = $wpdb->posts.ID
AND tt_$taxonomy.term_taxonomy_id = tr_$taxonomy.term_taxonomy_id
AND tt_$taxonomy.taxonomy = '$taxonomy'
AND t_$taxonomy.term_id = tt_$taxonomy.term_id
)
";
}
$clauses['groupby'] = 'wptests_posts.ID';
return $clauses;
}
}
参考文献
注：本文内容整合自 Google/Baidu/Bing 辅助翻译的英文资料结果。如果您对结果不满意，可以加入我们改善翻译效果：薇晓朵技术论坛。