mysql - SQL Wordpress 查询 - 内连接与外连接

Question

我正在尝试获取 wp_postmeta 表中包含 (meta_key='propdetails' AND meta_value LIKE '%Vacant Unrented Ready%') 或 (meta_key='featured ' AND meta_value='on')。

我现有的查询(如下)正在运行，除非元键“特色”行缺失。然后，不会返回该帖子。

类似地，对于匹配行，我还需要检索我想要获取 meta_value where meta_key='martygeocoderlatlng'。但是，如果缺少该 meta_key，则根本不会返回整篇文章。

所以我需要使用我现有的查询并将 meta_key='featured' 或 meta_key='martygeocoderlatlng' 设为可选，所以如果它们不存在，我仍然可以进行我的其他比较并获得我需要的数据。

(我认为这可以通过 OUTER JOIN 以某种方式完成，但我不能完全理清语法。)

这是我现有的查询，它只返回 wp_postmeta 表中存在 meta_key='featured' 和 meta_key='martygeocoderlatlng' 的行(当然，满足 WHERE 子句中的其他条件):

SELECT 
    p.*,
    pm.*,
    pm_featured.meta_value AS featured,
    pm_latlng.meta_value AS latlng

FROM 
    wp_posts p
    JOIN wp_postmeta pm
      ON pm.post_id = p.ID
    JOIN wp_postmeta pm_propdetails
      ON (pm_propdetails.post_id = p.ID AND pm_propdetails.meta_key = 'propdetails')
    JOIN wp_postmeta pm_featured
      ON (pm_featured.post_id = p.ID AND pm_featured.meta_key = 'featured-property')
    JOIN wp_postmeta pm_latlng
      ON (pm_latlng.post_id = p.ID AND pm_latlng.meta_key = 'martygeocoderlatlng')

    JOIN wp_term_relationships tr
      ON tr.object_id = p.ID
    JOIN wp_term_relationships tr_taxrel
      ON (tr_taxrel.object_id = p.ID AND tr_taxrel.term_taxonomy_id = 12)

WHERE
    (pm_propdetails.meta_value LIKE '%Vacant Unrented Ready%'
        OR
     pm_featured.meta_value = 'on')

    AND p.post_type = 'property'
    AND p.post_status = 'publish'

GROUP BY p.ID
ORDER BY p.post_date DESC

Answer 1

这是一个非常激烈的查询。在不更好地了解您的表结构的情况下，很难确切知道如何有效地执行此操作(似乎可以组合其中的一些 JOINS)。

这里是通过编辑您的原始查询进行的尝试:

SELECT 
    p.*,
    pm.*,
    pm_featured.meta_value AS featured,
    pm_latlng.meta_value AS latlng

FROM 
    wp_posts p
    JOIN wp_postmeta pm
      ON pm.post_id = p.ID
    JOIN wp_postmeta pm_propdetails
      ON (pm_propdetails.post_id = p.ID AND pm_propdetails.meta_key = 'propdetails')
    LEFT JOIN wp_postmeta pm_featured
      ON (pm_featured.post_id = p.ID AND pm_featured.meta_key = 'featured-property')
    LEFT JOIN wp_postmeta pm_latlng
      ON (pm_latlng.post_id = p.ID AND pm_latlng.meta_key = 'martygeocoderlatlng')

    JOIN wp_term_relationships tr
      ON tr.object_id = p.ID
    JOIN wp_term_relationships tr_taxrel
      ON (tr_taxrel.object_id = p.ID AND tr_taxrel.term_taxonomy_id = 12)

WHERE
    (pm_propdetails.meta_value LIKE '%Vacant Unrented Ready%'
        OR
     (pm_featured.meta_value = 'on' OR pm_feature.meta_value IS NULL))

    AND p.post_type = 'property'
    AND p.post_status = 'publish'

GROUP BY p.ID
ORDER BY p.post_date DESC

它只是使用 LEFT JOIN 尝试返回可能缺少 meta_key 的帖子，然后修改 WHERE 部分以尝试说明 pm_featured.meta_value 是否为 NULL。

如果您提供更多信息，可以尝试其他版本。

编辑:根据您的问题，这也可能有效。虽然真的只是黑暗中的一个巨大镜头 =)

SELECT T2.*, T1.featured, T1.latlng FROM
(
    SELECT post_id, SUM(IF((meta_key='propdetails' AND meta_value LIKE '%Vacant Unrented Ready%') OR ((meta_key='featured' AND meta_value='on')),1,0)) AS Keeper
    , MAX(IF(meta_key = 'featured-property',meta_value,NULL)) AS featured
    , MAX(IF(meta_key = 'martygeocoderlatlng',meta_value,NULL)) AS latlng
    FROM wp_postmeta 
    GROUP BY post_id
    HAVING Keeper>0
) AS T1
JOIN
wp_posts AS T2
ON T1.post_id=T2.ID
AND T2.post_type = 'property'
AND T2.post_status = 'publish'
JOIN
wp_term_relationships AS T3
ON T2.ID=T3.object_id AND T3.term_taxonomy_id = 12
GROUP BY T2.ID
ORDER BY T2.post_date DESC

由于第二个查询最终起作用(有点令人惊讶，我做了很多假设；)这个查询应该稍微快一点并且更容易理解......(注意:特别是如果您在 wp_postmeta 表中的 (meta_key,meta_value) 上有一个复合索引。)

SELECT T2.*, T1.featured, T1.latlng FROM
(
    SELECT postmetas_info.post_id
    , MAX(IF(meta_key = 'featured-property',meta_value,NULL)) AS featured
    , MAX(IF(meta_key = 'martygeocoderlatlng',meta_value,NULL)) AS latlng
    FROM 
    (
        SELECT DISTINCT post_id FROM wp_postmeta WHERE (meta_key='propdetails' AND meta_value LIKE '%Vacant Unrented Ready%') OR (meta_key='featured' AND meta_value='on')
    ) AS postmetas_applicable
    JOIN
    wp_postmeta AS postmetas_info
    ON postmetas_applicable.post_id=postmetas_info.post_id
    GROUP BY postmetas_info.post_id
) AS T1
JOIN
wp_posts AS T2
ON T1.post_id=T2.ID
AND T2.post_type = 'property'
AND T2.post_status = 'publish'
JOIN
wp_term_relationships AS T3
ON T2.ID=T3.object_id AND T3.term_taxonomy_id = 12
GROUP BY T2.ID
ORDER BY T2.post_date DESC

上一个查询中的 SUM(IF()) 很有趣，但性能可能不如预期，因为它会计算表中的每一行。上面的查询首先削减了 post_id，然后仅根据符合条件的 post_id 获取相应的数据。