1

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.

  • your date format should be yyyymmdd to order correctly. for complex meta queries like you require, you'll have to modify the SQL directly via filters, WP_Query can't handle that. – Milo Nov 21 '14 at 19:51
  • @Milo Quite right - I was typing this from memory. My date formats are yyyymmdd in my code - updated my post to reflect this. As for the second part of your comment... damnit. Iwas debating if there was some other way I could write secondary meta keys that I could query or something... – indextwo Nov 21 '14 at 19:55
  • As Milo mentioned it may be worth using global $wpdb;. You could get the wp_query as close as possible, then print_r() to get the sql value, then modify the $sql string and run it through like $result = $wpdb->get_results($sql, OBJECT); – Xtremefaith Nov 21 '14 at 19:56
  • 1
    Also have you seen this post yet: Nested meta_query with multiple relation keys – Xtremefaith Nov 21 '14 at 19:59
  • @Xtremefaith That doesn't sound like a bad idea; I'm just dubious as to how well a method like that would play with pagination. This is all done on a standard archive page, hence using pre_get_posts rather than a custom query. With regards to the link: I tried nested meta queries too! At least there's confirmation it's a big fat nope, and relation is always a single top-level argument. Worth knowing. – indextwo Nov 21 '14 at 20:01
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/

  • I like that the very first thing mentioned is the use of nested Meta Queries! I'll have to check with the client though, as they'll have to wait until at least Dec 8th until I can put this on a production server. Thanks for the heads up! – indextwo Nov 22 '14 at 10:47
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.