Fix for select returning no result(s)-CodePudding

This is kind of a continuation of a question I asked earlier.

I am trying to implement a solution using raw sql on a WordPress database

When I run a query that looks like this

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')

I get the following results

 --------- --------- ------------------ ------------ 
| 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 |
 --------- --------- ------------------ ------------

But when I run the following

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))

I get an empty result set

Empty set (0.00 sec)

Is there a way to improve the no results query to get results that reflect what the snippet below indicates

current_date >= promo_start_date AND current_date <= promo_end_date

CodePudding user response：

The WHERE clause works on one row at a time. By definition, meta_key can have only one value on a given row. It cannot be both 'promo_start_date' and 'promo_end_date' on the same row. So checking that meta_key equals one value AND meta_key equals a different value will always be false.

What you need to do is check if meta_key is equal to one OR the other string. You can combine that with additional conditions depending on which value it is.

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))
  OR (meta_key = 'promo_end_date'
       AND CURRENT_DATE <= CAST(meta_value AS DATE)))

Alternative way of writing the same logic:

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