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