how to group custom post type posts by custom taxonomy terms

Question

I would really appreciate if you could help me :) I'm very stuck and don't know how to proceed.

I have a custom post type called event and 2 custom taxonomies registered for it:

1. event_type with tag-like functionality (ex. of event type: yoga, voice work)
2. event_location with category-like functionality (ex of event location: uk->brighton, london; europe->paris).

There are pages on the website like Yoga -> Upcoming events; Voice work -> Upcoming workshops. Depending on which page we are on I'd like to display appropriate events. For example, if we're on page Yoga -> Upcoming events I'd like to display all event custom posts with event_type being yoga and then group the results by event_location taxonomy terms. For example, list all yoga events for UK, Brighton then UK, London and then Europe, Paris and so on.

What I currently do in a template is the following:

1. I get the slug of the current page and its ancestors.
2. I get slugs of all terms in the event_type taxonomy, that have some events associated with them.
3. I determine which event types to display, by intersecting previously gathered values.
4. Then I construct a WP_Query to get event posts with determined event_type taxonomy terms.

This seems to work. However, results came back unordered and I would like them to be ordered by event_location taxonomy terms. For example:

• UK

  • Brighton
    • Event 1
    • Event 2
  • London
    
    • Event 3

• Europe

  • Paris
    • Event 4

I would also like to display only non-expired event posts and order them by event_date. The event_date value is stored as meta value along with the rest of event post details under a single meta_key.

Here is my code so far:

    //pull in the appropriate events depending on the page type: yoga/voice work
    /* get the ancestors and current page slugs */
    // collect ancestor pages
    $relations = get_post_ancestors($post->ID);
    // add current post to pages
    array_push($relations, $post->ID);
    // get pages slugs
    $relations_slugs = array();
    foreach($relations as $page_ID){
        $page = get_page($page_ID);
        array_push($relations_slugs, $page->post_name);
    }

    /* get the term slugs (non-empty) for event_type taxonomy */
    //get the event_type taxonomy values
    $event_types = get_terms( 'event_type', 'hide_empty=true' );
    $event_types_slugs = array();
    foreach($event_types as $event_type){
        array_push($event_types_slugs, $event_type->slug);
    }

    /* find what event_type to display on the current page */
    $page_event_types = array_intersect($relations_slugs, $event_types_slugs);
    $page_event_types_string = implode(",",$page_event_types);

    /* find the events of the event_type to display on the current page */
    $args = array(
        'post_type' => 'event',
        'tax_query' => array(
            array(
                'taxonomy' => 'event_type',
                'field' => 'slug',
                'terms' => $page_event_types_string
            )
        )
    );
    $temp = $wp_query; //assign orginal query to temp variable for later use   
    $wp_query = null;
    $wp_query = new WP_Query($args);

    /* run the loop to output the event's details */
    get_template_part( 'loop', 'event-details' );

I'm not great at constructing SQL statements. I would really appreciate if you could help me. Thank you very much!!!

EDIT

I have significantly simplified the functionality (as I got quite confused and lost in the original one). Please see my answer.

Thanks to @Bainternet for taking the time and trying to help me out!

Answer 1

try using this fancy function that group the posts by term id that Scribu and Mike created:

function event_clauses( $clauses, $wp_query ) {
    global $wpdb;

    if ( isset( $wp_query->query['orderby'] ) && 'event_location' == $wp_query->query['orderby'] ) {

        $clauses['join'] .=<<<SQL
LEFT OUTER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID={$wpdb->term_relationships}.object_id
LEFT OUTER JOIN {$wpdb->term_taxonomy} USING (term_taxonomy_id)
LEFT OUTER JOIN {$wpdb->terms} USING (term_id)
SQL;
        $clauses['where'] .= " AND (taxonomy = 'event_location' OR taxonomy IS NULL)";
        $clauses['groupby'] = "object_id";
        $clauses['orderby']  = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC) ";
        $clauses['orderby'] .= ( 'ASC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
    }

    return $clauses;
}

add to your args array 'orderby' => 'event_location'

and before the query add

 add_filter( 'posts_clauses', 'event_clauses', 10, 2 );

after the query add

remove_filter( 'posts_clauses', 'event_clauses');

Answer 2

I couldn't get the filter working as @Bainternet suggested. I've tried building the whole SQL, but I'm sure I'm doing something wrong as it doesn't work :S Would really appreciate any help. Thanks!

I've hard coded the event_type and event_date meta key value for the time being.

  SELECT posts.*
  FROM wp_posts as posts
  LEFT OUTER JOIN wp_postmeta as postmeta ON posts.ID = postmeta.post_id
  LEFT OUTER JOIN wp_term_relationships as relationships ON posts.ID = relationships.object_id
  LEFT OUTER JOIN wp_term_taxonomy as taxonomy ON relationships.term_taxonomy_id = taxonomy.term_taxonomy_id
  LEFT OUTER JOIN wp_terms as terms ON taxonomy.term_id = terms.term_id
  WHERE posts.post_type = 'event' AND posts.post_status = 'publish' 
  AND postmeta.meta_key = 'event_date' AND postmeta.meta_value >= '2011-02-02'
  AND terms.slug = 'kirtan' AND taxonomy.taxonomy = 'event_type' AND taxonomy.count > 0
  GROUP BY taxonomy.taxonomy
  ORDER BY terms.slug ASC, postmeta.meta_value ASC

Answer 3

I got a bit confused with this one. At the end I've decided not to group the events by event_location category and simplify the functionality.

What I have done instead is the following:

1. I detect what event_type events to display by looking at a URL. Then I display all the events of that event_type and order them by event_date ASC - the ones that coming soon appear first. Here the URL is of format /events?type=event_type

2. I present a sidebar with all the event_location category terms. A user can see all events in a specific location by clicking on the links. The URL in this case has the following format: /events?location=event_location. Events of all types but for that specific location are displayed ordered ASC (the ones that coming soon appear first).

Then in my custom query I include 'tax_query'. For example, for the event_location taxonomy something like:

$wp_query['tax_query'] = array('taxonomy' => 'event_location',
                               'field' => 'slug',
                               'terms' => $_GET['location']
                              )

Hope that helps someone.