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 = "(
			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)
		$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)

  • spacer sebastien says:
    5 Mar 2011 at 9:31 pm

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

  • spacer sebastien says:
    5 Mar 2011 at 11:26 pm

    it works perfectly

    • spacer scribu says:
      6 Mar 2011 at 3:30 am

      Cool. You might want to try the derived table method (updated the post).

  • spacer 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.

  • spacer goto10 says:
    7 Mar 2011 at 5:18 pm

    Thanks scribu. You’re a juggernaut!

  • spacer 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:


    Hope this helps.

    • spacer 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.

    • spacer 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. :)

      • spacer 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.

        • spacer 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.

          • spacer 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

    • spacer 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.

  • spacer Mike Schinkel says:
    8 Mar 2011 at 3:51 am

    Cool. Wanna post your updates? Also, the enlightenment is mutual.

    • spacer scribu says:
      8 Mar 2011 at 3:58 am

      Already did. Also see my last reply to your first comment.

      • spacer Mike Schinkel says:
        8 Mar 2011 at 4:02 am


        Also, look for an email from me on another subject…

  • spacer 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 by
    wp_posts.ID by wp_demo_posts.ID

    and that’s ok now !

    really cool !! Thanks

  • spacer sebastien says:
    8 Mar 2011 at 2:40 pm

    could i replace

    wp_posts.ID by $wpdb->posts.ID
    and by $wpdb->

    is it ok ?

    • spacer scribu says:
      8 Mar 2011 at 4:55 pm

      Yes, it’s recommended in fact. Post updated.

  • spacer Manny Fleurmond says:
    13 May 2011 at 4:42 pm

    Question: what does the GROUP_CONCAT do in the order by clause?

    • spacer scribu says:
      13 May 2011 at 5:08 pm

      It concatenates all the terms for a particular post into a single string.

  • spacer 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.

    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?

    • spacer 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.

      • spacer 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?

        • spacer scribu says:
          5 Jun 2011 at 2:42 am

          No, PHP 5.3 is not required.

      • spacer 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:


        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)……….

        • spacer 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.

          • spacer shawn says:
            5 Jun 2011 at 4:57 am

            Ahh that tells me exactly what I needed to know. Thanks so much

  • spacer 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!

  • spacer Manny Fleurmond says:
    2 Sep 2011 at 12:11 am

    Is there a way to sort by more than one taxonomy

  • spacer simon says:
    21 Nov 2011 at 11:57 am

    Exactly what I needed :-) Thanks

  • About
  • Contact
  • Archive is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.