Sortable Taxonomy Columns

After finding out how to make sortable columns based on custom fields, several people have asked how they could sort posts by taxonomy terms. So here’s how to do it:

Let’s presume we have a ‘product’post type, with a ‘color’taxonomy. Here’s some example code, just to get that out of the way.

So we already have a sortable ‘Color’column, except clicking on it sorts posts by date. Let’s fix that.

Orderby subquery

function color_orderby( $orderby, $wp_query ) {
	global $wpdb;
 
	if ( isset( $wp_query->query['orderby'] ) && 'color' == $wp_query->query['orderby'] ) {
		$orderby = "(
			SELECT GROUP_CONCAT(name ORDER BY name ASC)
			FROM $wpdb->term_relationships
			INNER JOIN $wpdb->term_taxonomy USING (term_taxonomy_id)
			INNER JOIN $wpdb->terms USING (term_id)
			WHERE $wpdb->posts.ID = object_id
			AND taxonomy = 'color'
			GROUP BY object_id
		) ";
		$orderby .= ( 'ASC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
	}
 
	return $orderby;
}
add_filter( 'posts_orderby', 'color_orderby', 10, 2 );

Even if they have more than one term associated, the posts will be sorted correctly.

However, the query is not very efficient, since we’re basically doing a SELECT for each post. It will get very slow if you have thousands of posts. Let’s see if we can do better.

Derived table

function color_clauses( $clauses, $wp_query ) {
	global $wpdb;
 
	if ( isset( $wp_query->query['orderby'] ) && 'color' == $wp_query->query['orderby'] ) {
		$clauses['join'] .= " LEFT JOIN (
			SELECT object_id, GROUP_CONCAT(name ORDER BY name ASC) AS color
			FROM $wpdb->term_relationships
			INNER JOIN $wpdb->term_taxonomy USING (term_taxonomy_id)
			INNER JOIN $wpdb->terms USING (term_id)
			WHERE taxonomy = 'color'
			GROUP BY object_id
		) AS color_terms ON ($wpdb->posts.ID = color_terms.object_id)";
		$clauses['orderby'] = 'color_terms.color ';
		$clauses['orderby'] .= ( 'ASC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
	}
 
	return $clauses;
}
add_filter( 'posts_clauses', 'color_clauses', 10, 2 );

This is apparently faster, since we’re getting all the terms at once, but it still won’t scale, because a temporary table has to be created, which takes longer and longer, the more connections between posts and terms you have.

Direct JOINs

Mike Schinkel droped by and left an improved method in the comments. I just cleaned it up a bit and made it work with posts that don’t have any terms associated. Enjoy:

function color_clauses_mike( $clauses, $wp_query ) {
	global $wpdb;
 
	if ( isset( $wp_query->query['orderby'] ) && 'color' == $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 = 'color' 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_filter( 'posts_clauses', 'color_clauses_mike', 10, 2 );

Comments (30)

sebastien says:

5 Mar 2011 at 9:31 pm

Mr Scribu, you are a king ! I try this. Thank you very much.
sebastien says:

5 Mar 2011 at 11:26 pm

it works perfectly
- scribu says:
  
  6 Mar 2011 at 3:30 am
  
  Cool. You might want to try the derived table method (updated the post).
Jeff says:

6 Mar 2011 at 6:34 pm

Thank you, after reading the other article on custom sortable columns (and converting to unix timestamps because I couldn’t get this to work), this is a very clear explanation and very useful.
goto10 says:

7 Mar 2011 at 5:18 pm

Thanks scribu. You’re a juggernaut!
Mike Schinkel says:

8 Mar 2011 at 2:50 am

Nice. I learned two things here:

1.) I didn’t know the USING clause existed (me being from a SQL Server background for my first 10 years of SQL), and

2.) I didn’t realize that ‘posts_clauses’had made it into 3.1; awesome!

OTOH, your second example is still inefficient SQL; just run an EXPLAIN on it to see why. Here’s a modification of your code that generates more efficient SQL:

- https://gist.github.com/18373486fb1900319f2a

Hope this helps.
- scribu says:
  
  8 Mar 2011 at 3:16 am
  
  Indeed, after testing with a larger data set, the derived table query took around 26 seconds, 25 of which was spent creating the temporary table.
  
  Your method is a lot faster, but if a post has no terms associated, it’s not displayed at all.
- scribu says:
  
  8 Mar 2011 at 3:30 am
  
  It turns out it was just a matter of adding OR taxonomy IS NULL. I updated the post.
  
  Thanks for the enlightment. :)
  - Hunter Satterwhite says:
    
    31 Mar 2011 at 4:09 am
    
    Hey scribu,
    
    Great tutorial and I appreciate the updates that include Mike’s code. I tried the latest SQL code, but when I sort a column ASC/DESC entries that are NULL disappear after the page reloads with the new sort even though taxonomy IS NULL is included in the WHERE clause.
    - scribu says:
      
      1 Apr 2011 at 3:22 am
      
      I’ve just tested all the 3 code samples from the post again and they all produce identical (and correct) results.
      - gbee says:
        
        27 Apr 2011 at 5:15 am
        
        im having the same problem as Hunter. when I sort on the column that has my custom tax, the entries that are null disappear after the sort.
        
        but im happy nonetheless. thank you
- scribu says:
  
  8 Mar 2011 at 3:48 am
  
  Interesting… the first method (orderby subquery) seems to be faster than the direct joins: 0.79 seconds vs. 1.22 seconds.
  
  Although it does many intermediate queries, they’re extremely fast.
  
  I’m only testing with 22.000+ posts and a handful of terms. It probably won’t look the same when there are say 100.000 posts or more.
Mike Schinkel says:

8 Mar 2011 at 3:51 am

Cool. Wanna post your updates? Also, the enlightenment is mutual.
- scribu says:
  
  8 Mar 2011 at 3:58 am
  
  Already did. Also see my last reply to your first comment.
  - Mike Schinkel says:
    
    8 Mar 2011 at 4:02 am
    
    Excellent.
    
    Also, look for an email from me on another subject…
sebastien says:

8 Mar 2011 at 2:37 pm

In a first time, the third code doesn’t work.
But i think that’s because i have change the prefix of my wp database (wp_demo_)

I have replace
wp_terms.name by wp_demo_terms.name
and
wp_posts.ID by wp_demo_posts.ID

and that’s ok now !

really cool !! Thanks
sebastien says:

8 Mar 2011 at 2:40 pm

could i replace

wp_posts.ID by $wpdb->posts.ID
and
wp_terms.name by $wpdb->terms.name

is it ok ?
- scribu says:
  
  8 Mar 2011 at 4:55 pm
  
  Yes, it’s recommended in fact. Post updated.
Manny Fleurmond says:

13 May 2011 at 4:42 pm

Question: what does the GROUP_CONCAT do in the order by clause?
- scribu says:
  
  13 May 2011 at 5:08 pm
  
  It concatenates all the terms for a particular post into a single string.
  
  dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
shawn says:

4 Jun 2011 at 10:33 am

Just to test this out I copied your example code to create the post_types and taxonomies for products and colors. I then created a number of products and assigned colors to each one.

I then added Mike’s sort code to my theme functions file as well.

When I visit the admin, all the products show up, and I can see the colors listed in the colors column.

Problem:
When I click on the arrow to sort via colors, all the posts disappear.

Running wp 3.2beta2 nightly, and I do have a custom db prefix wpdbtest_

Is there a step I am missing that would make the products disappear on the sort?
- scribu says:
  
  4 Jun 2011 at 10:51 am
  
  Don’t copy Mike’s code, copy the code in the post, which has been updated and tested.
  - shawn says:
    
    5 Jun 2011 at 2:38 am
    
    Sorry, I should have been more clear:
    I copied the function from the post, the 3rd example, ‘direct joins’.
    
    Not sure if it matters, but is php 5.3 required?
    - scribu says:
      
      5 Jun 2011 at 2:42 am
      
      No, PHP 5.3 is not required.
  - shawn says:
    
    5 Jun 2011 at 4:04 am
    
    I think I figured out what is going wrong, but not sure how to fix it:
    
    errors:
    
    WordPress database error Not unique table/alias: ‘wpm_3_term_relationships’for query SELECT SQL_CALC_FOUND_ROWS wpm_3_posts.* FROM wpm_3_posts LEFT OUTER JOIN wpm_3_term_relationships ON wpm_3_posts.ID=wpm_3_term_relationships.object_id
    LEFT OUTER JOIN wpm_3_term_taxonomy USING (term_taxonomy_id)……….
    - scribu says:
      
      5 Jun 2011 at 4:43 am
      
      That means you’re trying to sort the posts while already having filtered them by a certain category.
      
      I must admit I did not account for this case, since it doesn’t seem to be a useful behaviour.
      - shawn says:
        
        5 Jun 2011 at 4:57 am
        
        Ahh that tells me exactly what I needed to know. Thanks so much
kathy says:

25 Jul 2011 at 6:34 pm

applause! i’ve been wondering how to do that for a day now. thanks for sharing!
Manny Fleurmond says:

2 Sep 2011 at 12:11 am

Is there a way to sort by more than one taxonomy
simon says:

21 Nov 2011 at 11:57 am

Exactly what I needed :-) Thanks

Orderby subquery

Derived table

Direct JOINs

Comments (30)

Sortable Taxonomy Columns

WordPress Plugins

Other Projects

Featured Posts