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.

My goal is to have custom posts type's posts (CPT for simplicity) from a custom taxonomy (CT for simplicity) let say CT Hometown with slug Nebraska to be listed grouped by another CT, let say activity_categories. I've achieved 50% of the task, and i'm able to do the other 50% but not together on the same query.

Let see first case where I list all posts from my CPT grouped by a hometown's CT

$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$activities_args = array(
    'post_type' => 'activities',
    'paged' => $paged,
    'posts_per_page' => '15',
    'orderby'        => 'taxonomy.activity_categories',
    'ordertax'       => 'ASC',
    'order'          => 'DESC'
);

add_filter('posts_clauses', 'orderby_tax_clauses', 10, 2 );
$activities_query = new WP_Query( $activities_args );
remove_filter('posts_clauses', 'orderby_tax_clauses', 10, 2 );

if ( $activities_query->have_posts() ) {
    echo '<ul>';
    $current_taxonomy = '';
    while ( $activities_query->have_posts() ) {
        $activities_query->the_post();

        $first_activities_categories = '';
        $terms = get_the_terms( get_the_ID(), 'activity_categories' );

        if ( $terms && ! is_wp_error( $terms ) ) {
            foreach ( $terms as $term ) {
                if (empty($first_activities_categories)) {
                    $first_activities_categories = $term->name;
                    $first_activities_categories_desc = $term->description;
                }
            }
        }

        if($current_taxonomy != $first_activities_categories) {
            if (!empty($current_taxonomy)) { echo '</li></ul>'; }
            echo '<li><h2>'.$first_activities_categories.'</h2><p>'.$first_activities_categories_desc.'</p>';
            echo '<ul>';
            $current_taxonomy = $first_activities_categories;
        }
        echo '<li class="clearfix">';
        the_title( '<h3>', '</h3>' );
        the_excerpt();
        echo '</li>';
    } /* while ( have_posts() ) */
    echo '</ul></li></ul>';
} /* if ( have_posts() ) */
show_pagination($activities_query);

and in my functions.php

function orderby_tax_clauses( $clauses, $wp_query ) {
    $orderby_arg = $wp_query->get('orderby');
    if ( ! empty( $orderby_arg ) && substr_count( $orderby_arg, 'taxonomy.' ) ) {
        global $wpdb;
        $bytax = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC)";
        $array = explode( ' ', $orderby_arg );
        if ( ! isset( $array[1] ) ) {
            $array = array( $bytax, "{$wpdb->posts}.post_date" );
            $taxonomy = str_replace( 'taxonomy.', '', $orderby_arg );
        } else {
            foreach ( $array as $i => $t ) {
                if ( substr_count( $t, 'taxonomy.' ) )  {
                    $taxonomy = str_replace( 'taxonomy.', '', $t );
                    $array[$i] = $bytax;
                } elseif ( $t === 'meta_value' || $t === 'meta_value_num' ) {
                    $cast = ( $t === 'meta_value_num' ) ? 'SIGNED' : 'CHAR';
                    $array[$i] = "CAST( {$wpdb->postmeta}.meta_value AS {$cast} )";
                } else {
                    $array[$i] = "{$wpdb->posts}.{$t}";
                }
            }
        }
        $order = strtoupper( $wp_query->get('order') ) === 'ASC' ? ' ASC' : ' DESC';
        $ot = strtoupper( $wp_query->get('ordertax') );
        $ordertax = $ot === 'DESC' || $ot === 'ASC' ? " $ot" : " $order";
        $clauses['orderby'] = implode(', ',
            array_map( function($a) use ( $ordertax, $order ) {
                return ( strpos($a, 'GROUP_CONCAT') === 0 ) ? $a . $ordertax : $a . $order;
            }, $array )
        );
        $clauses['join'] .= " LEFT OUTER JOIN {$wpdb->term_relationships} as relationship ";
        $clauses['join'] .= "ON {$wpdb->posts}.ID = relationship.object_id";
        $clauses['join'] .= " LEFT OUTER JOIN {$wpdb->term_taxonomy} ";
        $clauses['join'] .= "USING (term_taxonomy_id)";
        $clauses['join'] .= " LEFT OUTER JOIN {$wpdb->terms} USING (term_id)";
        $clauses['groupby'] = "object_id";
        $clauses['where'] .= " AND (taxonomy = '{$taxonomy}' OR taxonomy IS NULL)";
    }
    return $clauses;
}

this part work really well but that remove me the posibillity of an tax_query args if I add :

'tax_query' => array(
    array(
        'taxonomy' => 'hometown',
        'field' => 'slug',
        'terms' => 'nebraska'
    )
)

to my $activities_args i receive this error

WordPress database error: [Column 'term_taxonomy_id' in from clause is ambiguous]
SELECT SQL_CALC_FOUND_ROWS wp_vnr_posts.ID FROM wp_vnr_posts 
INNER JOIN wp_vnr_term_relationships ON (wp_vnr_posts.ID = wp_vnr_term_relationships.object_id) 
LEFT OUTER JOIN wp_vnr_term_relationships as relationship ON wp_vnr_posts.ID = relationship.object_id 
LEFT OUTER JOIN wp_vnr_term_taxonomy USING (term_taxonomy_id) 
LEFT OUTER JOIN wp_vnr_terms USING (term_id) 
WHERE 1=1 AND ( wp_vnr_term_relationships.term_taxonomy_id IN (2) ) AND wp_vnr_posts.post_type = 'activities' AND (wp_vnr_posts.post_status = 'publish') AND (taxonomy = 'activity_categories' OR taxonomy IS NULL) 
GROUP BY object_id ORDER BY GROUP_CONCAT(wp_vnr_terms.name ORDER BY name ASC) ASC, wp_vnr_posts.post_date DESC LIMIT 0, 15

Thank's in advanced for everyone helping with this question !

share|improve this question
    
Does it have to be WP_Query, any reason why not use the wpdb object? –  gdaniel Jul 3 at 20:49
    
Are you trying to get a list of cities (CT) with posts (CPT) grouped by activity(CT)? –  rafawhs Jul 11 at 13:23

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.