修复WordPress数据库SQL查询无结果问题：筛选有效促销活动

这是此前提问的延续，我正尝试在WordPress数据库中使用原生SQL实现解决方案。

执行以下查询时：

SELECT *
FROM wp_postmeta
WHERE post_id IN
    (SELECT ID
     FROM wp_posts
     WHERE post_type = 'promotions'
       AND post_status = 'publish')
  AND meta_key IN ('promo_start_date',
                   'promo_end_date')

可得到正常结果：

+---------+---------+------------------+------------+
| meta_id | post_id | meta_key         | meta_value |
+---------+---------+------------------+------------+
| 6199874 |   54679 | promo_end_date   | 2023-01-01 |
| 6199876 |   87246 | promo_end_date   | 2023-01-02 |
| 6199878 |   87251 | promo_end_date   | 2023-01-03 |
| 6199880 |   87255 | promo_end_date   | 2023-01-04 |
| 6199882 |   87257 | promo_end_date   | 2023-01-05 |
| 6199873 |   54679 | promo_start_date | 2022-12-14 |
| 6199875 |   87246 | promo_start_date | 2022-12-15 |
| 6199877 |   87251 | promo_start_date | 2022-12-16 |
| 6199879 |   87255 | promo_start_date | 2022-12-17 |
| 6199881 |   87257 | promo_start_date | 2022-12-18 |
+---------+---------+------------------+------------+

但执行以下添加日期条件的查询时：

SELECT *
FROM wp_postmeta
WHERE post_id IN
    (SELECT ID
     FROM wp_posts
     WHERE post_type = 'promotions'
       AND post_status = 'publish')
  AND (meta_key = 'promo_start_date'
       AND CURRENT_DATE >= CAST(meta_value AS DATE))
  AND (meta_key = 'promo_end_date'
       AND CURRENT_DATE <= CAST(meta_value AS DATE))

返回空结果集：

Empty set (0.00 sec)

请问如何优化该查询，以获取符合current_date >= promo_start_date AND current_date <= promo_end_date条件的结果？

错误原因 #

原查询逻辑矛盾：一条wp_postmeta记录的meta_key不可能同时等于promo_start_date和promo_end_date，用AND连接两个互斥的条件必然返回空集。

优化后的查询写法 #

写法一：先筛选有效促销ID，再关联元数据

先通过JOIN筛选出符合日期条件的促销ID，再获取对应的起止日期元数据：

SELECT pm.*
FROM wp_postmeta pm
INNER JOIN (
    SELECT p.ID
    FROM wp_posts p
    -- 关联并筛选开始日期
    INNER JOIN wp_postmeta start_meta 
        ON p.ID = start_meta.post_id 
        AND start_meta.meta_key = 'promo_start_date' 
        AND CURRENT_DATE >= CAST(start_meta.meta_value AS DATE)
    -- 关联并筛选结束日期
    INNER JOIN wp_postmeta end_meta 
        ON p.ID = end_meta.post_id 
        AND end_meta.meta_key = 'promo_end_date' 
        AND CURRENT_DATE <= CAST(end_meta.meta_value AS DATE)
    WHERE p.post_type = 'promotions' 
      AND p.post_status = 'publish'
) valid_posts ON pm.post_id = valid_posts.ID
WHERE pm.meta_key IN ('promo_start_date', 'promo_end_date')

写法二：用条件聚合合并日期后筛选

将同一促销的起止日期合并到一行，再进行日期条件筛选：

SELECT 
    post_id,
    MAX(CASE WHEN meta_key = 'promo_start_date' THEN meta_value END) AS promo_start_date,
    MAX(CASE WHEN meta_key = 'promo_end_date' THEN meta_value END) AS promo_end_date
FROM wp_postmeta
WHERE post_id IN (
    SELECT ID FROM wp_posts WHERE post_type = 'promotions' AND post_status = 'publish'
)
AND meta_key IN ('promo_start_date', 'promo_end_date')
GROUP BY post_id
HAVING CURRENT_DATE >= CAST(promo_start_date AS DATE) 
   AND CURRENT_DATE <= CAST(promo_end_date AS DATE)

内容的提问来源于stack exchange，提问作者kellymandem