Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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!

share|improve this question
1  
Try taking a look at the posts_groupby filter. Here's a related WPXchg link: wordpress.stackexchange.com/questions/4528/… –  hsatterwhite Mar 18 '11 at 13:43

3 Answers 3

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');
share|improve this answer
    
Thanks for the reply. I've done as you said, but now query doesn't seem to return anything :S –  dashaluna Mar 21 '11 at 12:52
    
@Bainternet I finally managed to see what SQL gets generated in WP_Query[request]. Somehow I end up with 2 statements of JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) I guess the inner join comes from the previously set query vars. I've deleted one of them and was left with: –  dashaluna Mar 21 '11 at 16:10
    
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT OUTER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT OUTER JOIN wp_term_taxonomy USING (term_taxonomy_id) LEFT OUTER JOIN wp_terms USING (term_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (3) ) AND wp_posts.post_type = 'event' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (taxonomy = 'event_location' OR taxonomy IS NULL) GROUP BY object_id ORDER BY GROUP_CONCAT(wp_terms.name ORDER BY name ASC) DESC LIMIT 0, 10 –  dashaluna Mar 21 '11 at 16:11
    
@Bainternet, this doesn't return anything either :S –  dashaluna Mar 21 '11 at 16:12
    
@Bainternet I think the following line is to blame wp_term_relationships.term_taxonomy_id IN (3). Here '3' refers to the ID of the event_type. I think as soon as SQL selected the records in event_type taxonomy, it then can't refer to taxonomy table again to look up for event_location. I don't know if self-reference will help here? –  dashaluna Mar 21 '11 at 16:37

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
share|improve this answer
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.