SQL WordPress 自定义查询内连接

Question

我的 SQL 语句遇到一些困难。我正在 WordPress 上进行查询，以根据多个帖子元字段显示帖子。当我仅使用一个元字段或多个元字段进行查询和过滤时，它可以工作，但是多个元字段上的 AND 会失败。

SELECT wposts . *
FROM wp_posts wposts
INNER JOIN (
  SELECT post_id
  FROM wp_postmeta wpostmeta
  WHERE (
    (wpostmeta.meta_key = 'ulnooweg_business_industry'
      AND wpostmeta.meta_value = 'Legal Services')
    AND (
    wpostmeta.meta_key = 'ulnooweg_business_province'
      AND wpostmeta.meta_value = 'New Brunswick')
  )
  GROUP BY post_id
) 
AS t ON t.post_id = wposts.ID
WHERE wposts.post_status = 'publish'
AND wposts.post_type = 'business'
ORDER BY wposts.post_title ASC
LIMIT 0 , 30 

Answer 1

在子查询中，看起来它正在查找同时包含 wpostmeta.meta_key = 'ulnooweg_business_industry' 和 wpostmeta.meta_key = 'ulnooweg_business_province' 的记录 - 换句话说，wpostmeta.meta_key 需要同时等于两个字符串才能满足此条件。此外，它还在寻找 wpostmeta.meta_value = 'Legal Services' 和 wpostmeta.meta_value = 'New Brunswick'。

我的猜测是，这就是您在子查询的 WHERE 子句中想要的 - 将 AND 之一更改为 OR：

....
WHERE (
  (wpostmeta.meta_key = 'ulnooweg_business_industry'
    AND wpostmeta.meta_value = 'Legal Services')
  OR ( -- changed to an OR
  wpostmeta.meta_key = 'ulnooweg_business_province'
    AND wpostmeta.meta_value = 'New Brunswick')
)
....

Answer 2

问题出在内部 select 的 where 子句中；我猜 wpostmeta 返回多行。之前关于字符串不能是两个值的评论是正确的。如果第一种方法不能

一开始我认为，

WHERE
  ((wpostmeta.meta_key = 'ulnooweg_business_industry' AND wpostmeta.meta_value = 'Legal Services') OR 
   (wpostmeta.meta_key = 'ulnooweg_business_province' AND wpostmeta.meta_value = 'New Brunswick'))
Group by Post_ID
HAVING count(post_ID) = 2

第二种方法应该有效。只有当每种类型的条目在 wpostmeta 中只有一条记录时，这种方法才有效。如果
postmeta.meta_key = 'ulnooweg_business_industry' AND wpostmeta.meta_value = 'Legal Services' 可以出现两次，那么上面的方法不起作用。

第二种方法

Select wposts.*
FROM WP_Posts wposts
INNER JOIN  (
Select POST_ID from WP_POSTMeta where meta_key = 'ulnooweg_business_industry' AND wpostmeta.meta_value = 'Legal Services'
INTERSECT
SELECT POST_ID FROM WP_POST_META WHERE meta_key = 'ulnooweg_business_province' AND wpostmeta.meta_value = 'New Brunswick'
) 
AS T on T.Post_ID = wposts.ID

Answer 3

您的查询正在测试meta_key（和meta_value）是否是同一行中的2个不同值，这是不可能的。但我明白你想要做什么..

尝试加入 wp_postmeta 表两次，但每次都使用 ON 子句，该子句排除除满足 meta_key 条件之外的所有行：

SELECT 
    p.*, 
    GROUP_CONCAT(CONCAT(pm.meta_key,':',pm.meta_value) SEPARATOR ',') AS meta_values
FROM 
    wp_posts p
    JOIN wp_postmeta pm ON pm.post_id = p.ID
    JOIN wp_postmeta pm_bi ON (pm_bi.post_id = p.ID AND pm_bi.meta_key = 'ulnooweg_business_industry')
    JOIN wp_postmeta pm_bp ON (pm_bp.post_id = p.ID AND pm_bp.meta_key = 'ulnooweg_business_province')
WHERE 
    pm_bi.meta_value = 'Legal Services'
    AND pm_bp.meta_value = 'New Brunswick'
    AND p.post_type = 'business'
    AND p.post_status = 'publish'
GROUP BY p.ID
ORDER BY p.post_title ASC

注意：我在这里加入了 wp_postmeta 表 3 次，以帮助证明满足条件，但您可以删除 GROUP_CONCAT 行（当然还有前一行上的逗号）和第一个 JOIN 到 wp_postmeta ，查询将同样工作。