So I actually went with $wpdb because I can combine that with some additional requirements I had. Here’s the query I ended up with:
$category_slug = $_GET["category"];
if($category_slug) {
$category_query = $wpdb->prepare("AND wp_terms.slug = %s", $category_slug);
} else {
$category_query = "";
}
$event_dates_query = <<<EOD
SELECT event_dates.*, $wpdb->terms.name AS category_name
FROM $wpdb->posts AS event_dates
INNER JOIN $wpdb->postmeta AS acf_event
ON acf_event.meta_key = 'event'
AND acf_event.post_id = event_dates.ID
INNER JOIN $wpdb->postmeta AS acf_date
ON acf_date.meta_key = 'date'
AND acf_date.post_id = event_dates.ID
INNER JOIN $wpdb->posts AS events
ON events.ID = acf_event.meta_value
INNER JOIN $wpdb->term_relationships
ON $wpdb->term_relationships.object_id = events.ID
INNER JOIN $wpdb->term_taxonomy
ON $wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id
INNER JOIN $wpdb->terms
ON $wpdb->term_taxonomy.term_id = wp_terms.term_id
WHERE (acf_date.meta_value >= %s
AND acf_date.meta_value < %s)
AND event_dates.post_type = 'event_date'
AND event_dates.post_status = 'publish'
$category_query
GROUP BY event_dates.ID
ORDER BY acf_date.meta_value ASC;
EOD;
$event_dates = $wpdb->get_results(
$wpdb->prepare($event_dates_query,
$first_day_of_month,
$last_day_of_month));
Besides joining various term-taxonomy-meta-tables I also filter the EventDates by their date, so I can paginate them by month.
So there’s quiet an ugly query sitting in my template now, but I think in terms of performance and lines of (php) code written that’s the best I can hope for here.
Based on this query I was also able to build a fulltext search where I query for attributes and meta-keys of Events and get back their related EventDates quiet easily, so thats nice too.
@hube2 thanks for your reply! This confirms my fear that WP_Query wont do triple joins and I have to work around that – the code sample looks helpful, I’m going to dig into that.
wpdb looks interesting too – I’m pretty comfortable tinkering with SQL queries so maybe there is a more elegant solution.
