按两个键/值对排序查询结果,其中键/值对的字段相同 [英] 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屋!