CODEWITHSUNDEEP
sqlwordpress
Dre
Dre

Reputation: 2953

How to perform multiple AND/OR meta_query using using $wpdb

SCENARIO: I have a custom post type with start and end dates saved as post meta. This is to allow the admin to schedule dates to show AND hide a post. There is also the option to leave these dates blank, in which case the post just shows by default. As meta_query doesn't appear to allow you to perform an AND/OR comparison, I've dabbled with creating a custom SQL query using $wpdb. SQL is new territory for me. Currently, this works for the start date:

$today = date('Y-m-d');
$results = $wpdb->get_results($wpdb->prepare("
    SELECT
        $wpdb->posts.post_title,
        $wpdb->posts.ID,
        $wpdb->posts.post_content
    FROM
        $wpdb->posts,
        $wpdb->postmeta

    WHERE
        $wpdb->posts.ID = $wpdb->postmeta.post_id
        AND $wpdb->posts.post_type = 'announcements'
        AND ($wpdb->postmeta.meta_key = 'sap_start_date'
            AND ($wpdb->postmeta.meta_value <= '$today' OR $wpdb->postmeta.meta_value = ''))
"));

However, when I try and amend the above to query the end date, I get nothing back. I tried changing the WHERE section to:

    WHERE
        $wpdb->posts.ID = $wpdb->postmeta.post_id
        AND $wpdb->posts.post_type = 'announcements'
        AND ($wpdb->postmeta.meta_key = 'sap_start_date'
            AND ($wpdb->postmeta.meta_value <= '$today' OR $wpdb->postmeta.meta_value = ''))
        AND ($wpdb->postmeta.meta_key = 'sap_end_date'
            AND ($wpdb->postmeta.meta_value >= '$today' OR $wpdb->postmeta.meta_value = ''))

This returns an empty array.

QUESTION: How would I write this query to check both the sap_start_date and sap_end_date post meta values?

Upvotes: 1

Views: 3201

Answers (1)

Dre
Dre

Reputation: 2953

I figured out my problem was that I was using a logical AND against the same meta key object twice; how can postmeta.meta_key be equal to start_date and end_date? Using an answer from this SO question I managed to re-write the query to use aliases so that I could get the postmeta twice and query the two different keys:

    SELECT
        $wpdb->posts.post_title,
        $wpdb->posts.ID,
        $wpdb->posts.post_content
    FROM
        $wpdb->posts,
        $wpdb->postmeta AS postmeta1,
        $wpdb->postmeta AS postmeta2

    WHERE
        $wpdb->posts.ID = postmeta1.post_id
        AND $wpdb->posts.ID = postmeta2.post_id
        AND $wpdb->posts.post_type = 'announcements'
        AND (postmeta1.meta_key = 'sap_start_date'
            AND (postmeta1.meta_value <= '$today' OR postmeta1.meta_value = ''))
        AND (postmeta2.meta_key = 'sap_end_date'
            AND (postmeta2.meta_value >= '$today' OR postmeta2.meta_value = ''))

This works, although I'm still a little fuzzy on just how it works. If anyone could shed some further light on this purely for educational purposes it would be much appreciated.

Upvotes: 2

Related Questions