how to group custom post type posts by custom taxonomy terms

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!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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');

Method 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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0