按两个键/值对排序查询结果，其中键/值对的字段相同 [英] Order query results by two key/value pairs, where the fields for the key/value pairs are the same

问题描述

我有一个查询来选择帖子(wp_posts 表)，其中第二个表(wp_postmeta)上存在 1 个键/值对或另一个.从本质上讲，这是可行的，但我需要按第一个键/对值 (department_head) 对结果进行排序，然后是第二个 (staff_surname).

I have a query to select posts (wp_posts table) where either 1 key/value pair or another is present on the a second table (wp_postmeta). Esentially, this works, but I then need to order the results by the first key/pair value (department_head), and then the second (staff_surname).

问题是，由于查询获取结果的方式，没有用于对结果进行排序的 department_head 键.我猜这是因为查询首先找到 staff_surname 键?

The problem is, becuause of the way the results are grabbed by the query, there is no department_head key to use to order the results by. I'm guessing that this is because the query is finding the staff_surname key first?

再次猜测，但如果我上面的猜测是正确的，那是因为，即使我两次加入表，MYSQL 只是从 wp_postmeta 表中抓取第一个实例，当 staff_surname 条件满足.这是我需要四处寻找的东西，以便我有正确的数据能够对结果进行排序.

Again guessing, but if my above guess is correct then it is because, even though I'm joining the table twice, MYSQL is just grabbing the first instance from the wp_postmeta table when the staff_surname condition is met. This is what I need to find away around, so that I have the correct data to be able to order the results.

完整查询 -

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
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_term_relationships.term_taxonomy_id IN (34)
)
AND wp_posts.post_type = 'people'
AND (
    wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'private'
)
AND (
    (
        wp_postmeta.meta_key = 'department_head'
        AND CAST(wp_postmeta.meta_value AS CHAR) = 'private-client'
    )
    OR (
        mt1.meta_key = 'staff_surname'
        AND CAST(mt1.meta_value AS CHAR) != ''
    )
)
GROUP BY wp_posts.ID
ORDER BY mt1.meta_value ASC
LIMIT 0, 10

查询结果示例-

ID  name   meta_id post_id meta_key    meta_value    meta_id  post_id  meta_key       meta_value

1   Test1  10      1       random_key  random_value  11       1        staff_surname  Smith
2   Test1  20      2       random_key  random_value  21       2        staff_surname  Jones
3   Test1  30      3       random_key  random_value  31       3        staff_surname  Harris

我需要的示例，如果琼斯是部门主管 -

Example of what I require, if Jones was the department head -

ID  name   meta_id post_id meta_key         meta_value      meta_id  post_id  meta_key       meta_value

1   Test1  10      1                                        11       1        staff_surname  Smith
2   Test1  22      2       department_head  private-client  21       2        staff_surname  Jones
3   Test1  30      3                                        31       3        staff_surname  Harris

department_head"键在绝大多数情况下不存在，这就是为什么键/值在其他结果中为空的原因.

The 'department_head' key does not exist in the vast majority of cases, that is why the key/value are blank on the other resutes.

有没有办法实现我需要的排序结果?

Is there a way to achieve the ordering results that I require?

注意:我在这里发布这个而不是 Wordpress 网站的原因是因为这是一个自定义查询，而不是 WP 生成的，所以它可能超出了大多数 WP 的范围专家.

Note: The reason that I have posted this here as opposed to the Wordpress site is because this is a custom query, not WP generated, so it will likely be out of the scope of most WP experts.

推荐答案

我不确定，但我会尝试使用一些 LEFT JOINS 来解决这个问题，并将条件移动到 JOINS 以提高查询效率.

I'm not sure but I will try to approach this with some LEFT JOINS and moving the conditions to the JOINS to make the query more efficient.

类似的东西可能会起作用...

Something similar to this may work...

SELECT p.*
FROM wp_posts p
INNER JOIN wp_term_relationships r ON 
    (p.ID=r.object_id AND r.term_taxonomy_id=34)
LEFT JOIN wp_postmeta m1 ON 
    (p.ID=m1.post_id AND m1.meta_key='department_head' AND CAST(m1.meta_value AS CHAR)='private-client')
LEFT JOIN wp_postmeta m2 ON 
    (p.ID=m2.post_id AND m2.meta_key = 'staff_surname' AND CAST(m2.meta_value AS CHAR) != '')
WHERE 
    p.post_type = 'people' 
  AND 
    (p.post_status = 'publish' OR p.post_status = 'private')
GROUP BY p.ID
ORDER BY m1.meta_value DESC, m2.meta_value DESC
LIMIT 0, 10

这篇关于按两个键/值对排序查询结果，其中键/值对的字段相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！