You may have noticed that when I was making Sortable Custom Columns I didn’t make it so I could sort some of the fields.

Showing sorted columns

That’s because unlike my lovely post meta, those other fields are custom taxonomies. And by their very nature, they’re not sortable. In order to do this however you need some SQL.

Make the Columns Sortable

First we have to add our new columns (lez_gender and lez_sexuality) into our sortable function.

add_filter( 'manage_edit-post_type_characters_sortable_columns', 'sortable_post_type_characters_column' );
function sortable_post_type_characters_column( $columns ) {
	unset( $columns['cpt-shows'] ); 			 	// Don't allow sort by shows
	$columns['postmeta-roletype']		= 'role';	// Allow sort by role
	$columns['taxonomy-lez_gender']		= 'gender';	// Allow sort by gender identity
	$columns['taxonomy-lez_sexuality']	= 'sex';	// Allow sort by gender identity
    return $columns;
}

Last time it only had the unset and the postmeta-roletype columns. Now we’re adding in our taxonomies as taxonomy-{TAXONOMYNAME} to that.

Actually Sort the Content

Now here’s the sneaky SQL stuff.

function lez_gender_clauses( $clauses, $wp_query ) {
	global $wpdb;

	if ( isset( $wp_query->query['orderby'] ) && 'gender' == $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 = 'lez_gender' 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', 'lez_gender_clauses', 10, 2 );

I repeat this with ‘gender’ changed to ‘sex’ and ‘lez_gender’ changed to ‘lez_sexuality’ for the other taxonomy.

I tested this on 921 posts in a custom post type and it didn’t make my server cry. Which is a bonus.

Reader Interactions

%d bloggers like this: