php - WordPress 自定义 SQL 案例排序优先级

Question

我希望有人能帮助我。我正在尝试在 WordPress 中执行自定义 SQL 查询，并按预定义的优先级对结果进行排序。

示例搜索字符串是“EU Directive”，查询需要查看帖子的标题和名为“intro”的自定义字段(文本字段)。

我需要通过对标题或简介以某种方式与完整字符串匹配的结果进行优先排序来进行排序，先于仅包含搜索字符串中 2 个单词中的 1 个单词的结果。

查询有效，但排序无效。仅匹配 2 个单词中的 1 个的结果将在“简介”字段中包含完整字符串“EU Directive”的结果之前被提取。

我认为下面的查询可以正确设置排序，但不幸的是，事实并非如此。

任何人都可以看到任何明显的错误吗？在此阶段，任何帮助将不胜感激。

SELECT * 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_title LIKE 'EU Directive %') OR ((wp_postmeta.meta_key = 'intro' AND     CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU Directive %'))     ) 
OR ( (wp_posts.post_title LIKE 'EU Directive%') OR ((wp_postmeta.meta_key = 'intro' AND     CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU Directive%'))  ) 
OR ( (wp_posts.post_title LIKE '% EU Directive %') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive %'))    ) 
OR ( (wp_posts.post_title LIKE '% EU Directive,%') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive,%'))    ) 
OR ( (wp_posts.post_title LIKE '% EU Directive.%') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive.%'))    ) 
OR ( (wp_posts.post_title LIKE '% EU Directive%') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive%'))  ) 
OR ( (wp_posts.post_title LIKE 'EU %') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU %'))    ) 
OR ( (wp_posts.post_title LIKE '% EU %') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU %'))    )
) 

AND wp_posts.post_type = 'documents' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID 

ORDER BY 

(CASE 

WHEN wp_posts.post_title LIKE 'EU Directive %' THEN 1 
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU Directive %') THEN 2 
WHEN wp_posts.post_title LIKE 'EU Directive%' THEN 3 
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU Directive%') THEN 4 
WHEN wp_posts.post_title LIKE '% EU Directive %' THEN 5 
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive %') THEN 6 
WHEN wp_posts.post_title LIKE '% EU Directive,%' THEN 7 
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive,%') THEN 8 
WHEN wp_posts.post_title LIKE '% EU Directive.%' THEN 9 
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive.%') THEN 10 
WHEN wp_posts.post_title LIKE '% EU Directive%' THEN 11 
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive%') THEN 12
ELSE 13
END), 
wp_posts.post_date DESC LIMIT 0, 10

Answer 1

如果我理解正确，在您的 CASE 语句中，您将尝试根据满足的条件输出排序值。问题是，从 CASE 语句中您得到一个数字:假设它是 1。在这种情况下，您只需执行 ORDER BY 1，这意味着“按第一个柱子”。您应该使用该 CASE 作为 SELECT 中的字段，然后将该字段的名称放入 ORDER BY 子句中。像这样的事情:

SELECT * FROM
    (SELECT *, (CASE WHEN... THEN ...) AS position FROM wp_posts ...) data
ORDER BY data.position, data.post_date DESC LIMIT 0, 10