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.
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.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...global $wpdb;
. You could get the wp_query as close as possible, thenprint_r()
to get the sql value, then modify the$sql
string and run it through like$result = $wpdb->get_results($sql, OBJECT);
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, andrelation
is always a single top-level argument. Worth knowing.