Home Forums General Issues Query posts through relationships' taxonomies Reply To: Query posts through relationships' taxonomies

  • 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-> 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'
          GROUP BY event_dates.ID
          ORDER BY acf_date.meta_value ASC;
      $event_dates = $wpdb->get_results(

    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.