Query posts through relationships' taxonomies

• cs

• January 28, 2017 at 2:09 am

So I created two custom post types:

1. Event: Has a title, a description
2. Event Date: Has two ACF fields: date and an Event relation. So each Event can have multiple Event Dates. If, for example, Madonna plays this and next saturday, the Event “Madonna in Concert” has two Event Dates

Additionally I use a taxonomy category to declare Events as “concerts”, “conferences”, “expositions” etc.

Now I’m listing all Event Dates on an archive page, sorted by their date, which works wonderfully. But now I’d like to add the option to only show “concerts”, so I’d like to filter those Event Dates by their Event‘s categories.

As I see it this endeavor requires at least two joins and I have no clue how I can do that with WP_Query or if that’s possible at all – do you have any idea how to solve that?

-CS

• John Huebner

• January 29, 2017 at 4:07 am

You are right, there isn’t any way to do this using WP_Query. Doing this on the event date archive is going to be extremely complicated. Well, doing it anywhere is going to be complicated because event category is not really related to the event date except through the event.

Doing this with just the basic function that WP gives us you would need to… and I’m not sure about this

1) Get all the even categories
2) Get all the events in each category
3) Somehow sort them by the event date and there isn’t any way to sort posts based on a field in another post

What you need to be able to do is one WP_query on the events. The best way to accomplish this is to store all of the information you need to query as part of the event.

I’ve posted several replies here about making repeaters easier to search and this is the same basic principle. You take information that you want and copy it to where it will help you the most.

add_action('acf/save_post', 'make_events_filterable');
function make_events_filterable($post_id) {
  if (get_post_type($post_id) != 'event') {
    // return;
  }
  // set up two fields to store the information
  // these need to have unique names that are different than the acf field names
  $category_field = 'event_category';
  $date_field = 'event_date';
  // delete anything that's currently stored in these two fields
  // to avoid creating duplicates
  delete_post_meta($post_id, $category_field);
  delete_post_meta($post_id, $date_field);

  // This is the complicated bit. You need to get the value the
  // category taxonomy field
  // and the date relationship field
  // loop through them, get the value you want to store from those locations
  // and then update the new post meta field with those values
  
      // start loop for a field
      // get value for the field
      add_post_meta($post_id, $field_name, $value, false);
      // false at the end means it can have multiple values
  
}

Now you can use these fields in WP order by clauses in the meta query to order your event posts by category and date https://make.wordpress.org/core/2015/03/30/query-improvements-in-wp-4-2-orderby-and-meta_query/

With them in the right order you can loop through them and output new category and date headings when they change.

Hope some of this helps.

• John Huebner

• January 29, 2017 at 4:08 am

Your other choice would be to use $wpdb and construct a custom query https://codex.wordpress.org/Class_Reference/wpdb

To be honest, I think my first way is easier since I’m getting pretty rusty at constructing MySQL queries.

• cs

• January 30, 2017 at 7:29 pm

@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.

• cs

• January 31, 2017 at 8:28 pm

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.

• John Huebner

• February 1, 2017 at 2:59 am

I’m glad you worked that out, even when I was working with MySQl directly on a regular bases I would not have been able to right that query, or at least not without it taking me much longer than it did you.

This will probably be a good example for others.