Using meta_query with multiple keys and compare values

Question

I suspect I may be asking for the moon on a stick with this, but:

I have a filter for my shows custom post type. The filter uses standard taxonomies attached to shows, but also allows the user to filter by Venue, which is a meta value for each show, or by Month, as there is a start date attached to each show.

The issue I've come across is that some shows may span multiple months; so, start the run in November 2014 and finish in December. For this I'm storing the meta values as follows:

startdate   => 20141225
enddate     => 20141224

Ideally, if someone filters shows for December 2014, I want to display shows that both start within December, or end before the end of December. The issue is that, because I'm also checking for the venue meta_value with relation => 'AND', I can't figure out a way to check for [pseudocode] WHERE venue == 'royal-playhouse' AND ((startdate BETWEEN array(20141201, 20141231)) OR (enddate BETWEEN array(20141201, 20141231))).

I'm using a pre_get_posts filter to modify the passed $query arguments, and it's worth noting that there are a couple more meta_value fields that are also used with the filter (such as show type, accessibility, etc.). Here's a simplified version of my code (which currently works perfectly, but only checks against startdate):

function showDates_orderQuery($query) {
    if ($query->is_main_query() && !is_admin() && is_post_type_archive('showdates')) {

        $queryVenue = $query->query_vars['venue'];
        $queryMonth = $query->query_vars['month'];

        $metaQuery = array();

        //  Venue meta

        if ($queryVenue) {
            $metaQuery[] = array(
                'key'       => 'venue',
                'value'     => array($queryVenue),
                'compare'   => 'IN',
            );
        }

        //  Month meta

        if ($queryMonth) {

            $monthEpoch = strtotime($queryMonth);
            $formattedQueryMonth = date('Ymd', $monthEpoch);
            $formattedEndOfQueryMonth = date('Ymt', $monthEpoch);

            $metaQuery['relation'] = 'AND'; //  Set because we want to check for shows from a venue AND by the given month

            $metaQuery[] = array(
                'key'       => 'startdate',
                'value'     => array($formattedQueryMonth, $formattedEndOfQueryMonth),
                'compare'   => 'BETWEEN',
                'type'      => 'DATE'
            );
        }

        if ($metaQuery) {
            $query->set('meta_query', $metaQuery);
        }

        $query->set('meta_key', 'startdate');
        $query->set('orderby', 'meta_value');
        $query->set('order', 'ASC');
    }
}

add_action('pre_get_posts', 'showDates_orderQuery');

Any ideas on how I might be able to achieve this would be gratefully received.

Answer 1

Depending on your timeline or how much you want to live on the edge, there were some great improvements that are set to be introduced to 4.1 that I think will allow you to do just what you're looking to do. If you want to live on the edge a little, you could develop with the 4.1 beta and switch to the release at the time of launch (be sure to file bug reports if you find them :) )

Here is a link to the make post that goes into a bit more detail: https://make.wordpress.org/core/2014/10/20/update-on-query-improvements-in-4-1/

Answer 2

You can't figure out a way to do that because there is no such way. It is not possible to mix ANDs and ORs like that with meta_query. The query system is great, but not all inclusive. You will need to use multiple queries, or filter the SQL yourself.

Answer 3

Because I am a complete idiot, I seem to have completely forgotten that I basically answered my own question over a year ago.

I was struggling to get the posts_join filter to behave (I generally have trouble with INNER JOINs anyway), and so instead looked to the posts_where filter. The main issue with it is that you can't pass any arguments to it other than the existing $where condition; and in this instance I wanted it to use non-global variables.

The answer was to use an inline anonymous Lambda function for the posts_where filter:

if ($queryMonth) {

    $monthEpoch = strtotime($queryMonth);
    $formattedQueryMonth = date('Ymd', $monthEpoch);
    $formattedEndOfQueryMonth = date('Ymt', $monthEpoch);

    $monthOptions = array(
        'monthstart' => $formattedQueryMonth,
        'monthend' => $formattedEndOfQueryMonth,
    );

    $monthFilter = function ($where = '') use ($monthOptions) {
        global $wpdb;
        $where .= " AND (($wpdb->postmeta.meta_key = 'startdate' AND $wpdb->postmeta.meta_value >= '" . $monthOptions['monthstart'] . "') AND ($wpdb->postmeta.meta_key = 'startdate' AND $wpdb->postmeta.meta_value <= '" . $monthOptions['monthend'] . "')) ";
        $where .= " OR (($wpdb->postmeta.meta_key = 'enddate' AND $wpdb->postmeta.meta_value >= '" . $monthOptions['monthstart'] . "') AND ($wpdb->postmeta.meta_key = 'enddate' AND $wpdb->postmeta.meta_value <= '" . $monthOptions['monthend'] . "')) ";
        return $where;
    };

    add_filter('posts_where', $monthFilter);
}

With any luck, this method should actually become completely redundant in the next few weeks thanks to the forthcoming release of 4.1 - thanks to @jmusal for the heads up.