Make a taxonomy column sortable in the admin?

Question

I have a taxonomy term I have set to show as a column in the admin UI for my custom post type:

add_action( 'init', 'create_asset_tax' );

function create_asset_tax() {
  register_taxonomy(
    'asset_type',
    'design_asset',
    array(
        'label' => __( 'Asset Type' ),
        'rewrite' => array( 'slug' => 'type' ),
        'show_admin_column' => true,
        'hierarchical' => true,
        'sort' => true
    )
  );
}

How would I make this column "sortable" in the admin UI for my custom post type? Most of the tips I've found here seem to be for sorting columns added, not for columns that result from a taxonomy term, so it's a bit confusing. Thanks.

Answer 1

You could use the posts_clauses filter:

function wpse155797_posts_clauses( $pieces, $query ) {
    if ( ! is_admin() || ! $query->is_main_query() ) {
        return $pieces;
    }
    global $wpdb;
    if ( ( $orderby = $query->get( 'orderby' ) ) == 'asset_type' ) {
        if ( ( $order = strtoupper( $query->get( 'order' ) ) ) != 'DESC' ) $order = 'ASC';
        $pieces[ 'join' ] .= ' LEFT JOIN ' . $wpdb->term_relationships . ' AS tr ON ' . $wpdb->posts . '.ID = tr.object_id'
            . ' LEFT JOIN ' . $wpdb->term_taxonomy . ' AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id'
            . ' LEFT JOIN ' . $wpdb->terms . ' AS t ON tt.term_id = t.term_id';
        $pieces[ 'fields' ] .= ', group_concat(t.name ORDER BY t.name ' . $order . ') AS ' . $orderby;
        $pieces[ 'groupby' ] = $wpdb->posts . '.ID';
        $pieces[ 'orderby' ] = $orderby . ' ' . $order . ', ' . $wpdb->posts . '.post_title ASC';
    }
    return $pieces;
}
add_filter( 'posts_clauses', 'wpse155797_posts_clauses', 10, 2 );

This assumes you've added

$columns['taxonomy-asset_type'] = 'asset_type';

to your manage_edit_sortable_columns filter.

This sorts by taxonomy terms but looks funny if you sort descending and have more than one term as each group still lists alphabetically. To fix this you need to override the default 'taxonomy-asset_type' column in your manage_posts_columns filter to be eg 'asset_type' (changing the manage_edit_sortable_columns column name above to match) and then duplicate the standard WP output code in your manage_posts_custom_column action with an added array_reverse on the output:

    case 'asset_type':
        $taxonomy = 'asset_type';
        if ( $terms = get_the_terms( $post->ID, $taxonomy ) ) {
            $out = array();
            foreach ( $terms as $t ) {
                $posts_in_term_qv = array();
                $posts_in_term_qv['taxonomy'] = $taxonomy;
                $posts_in_term_qv['term'] = $t->slug;

                $out[] = sprintf( '<a href="%s">%s</a>',
                    esc_url( add_query_arg( $posts_in_term_qv, 'upload.php' ) ),
                    esc_html( sanitize_term_field( 'name', $t->name, $t->term_id, $taxonomy, 'display' ) )
                );
            }
            // New bit
            if ( isset( $_GET['order'] ) && $_GET['order'] == 'desc' ) {
                $out = array_reverse( $out );
            }
            /* translators: used between list items, there is a space after the comma */
            echo join( __( ', ' ), $out );
        } else {
            echo '&#8212;';
        }
        break;