Ordering posts by custom taxonomy in admin area

Question

EDIT

@kaiser - this question is slightly different to the answer you posted in that I am talking about sorting by clicking the column title rather than adding a dropdown to filter by taxonomy term (which you'll see I've already done in the screenshot).

Like Otto says in the answer Milo linked to: 'it is not possible to order by taxonomy, because from a certain type of standpoint, that doesn't actually make much sense...the point of having a taxonomy on posts would really be to have terms in that taxonomy that are shared between posts'.

I see his point, as you can have multiple terms attached to a post, sorting by them is a bit pointless. I've decided to scrap the idea, so feel free to delete this post if it's of no use. Thanks.

ENDEDIT

Been scouring the net for the solution to this, but not finding the specific answer to my issue. I've registered a custom post type and a couple of custom taxonomies, but I'm trying to allow admins to sort posts by a taxonomy, just like you can with the Date column - see screenshot here:

Does anyone know how to do that? Here's how I'm registering the custom post type if it's of any use:

Class OSUFestivals
    {
        public static function init()
        {
            add_action( "init", array( __CLASS__, "register_directory_cpt" ) );
            add_action( "init", array( __CLASS__, "register_workshop_type_taxonomy" ) );
            add_action( "init", array( __CLASS__, "register_day_taxonomy" ) );
        }

        public static function register_directory_cpt()
        {
            $labels = array(
                'name'               => 'Festival Items',
                'singular_name'      => 'Festival Item',
                'menu_name'          => 'Festival',
                'name_admin_bar'     => 'Festival Item',
                'add_new'            => 'Add New Item',
                'add_new_item'       => 'Add New Item',
                'new_item'           => 'New Item',
                'edit_item'          => 'Edit Item',
                'view_item'          => 'View Item',
                'all_items'          => 'All Items',
                'search_items'       => 'Search Items',
                'parent_item_colon'  => 'Parent Items:',
                'not_found'          => 'No Items found.',
                'not_found_in_trash' => 'No Items found in Trash.',
            );
            $args = array(
                'labels'        => $labels,
                'public'        => true,
                'hierarchical'  => false,
                'has_archive'   => true,
                'show_in_admin_bar' => true,
                'menu_position' => 5,
                'supports'      => array( 'title', 'editor', 'thumbnail', 'author', 'excerpt', 'revisions', 'page-attributes' ),
                'rewrite'       => array(
                    'with_front'    => false,
                    'slug'          => 'festival-Items'
                )
            );

            register_post_type( 'lkfestival', $args );
        }

        public static function register_workshop_type_taxonomy()
        {
            $labels = array(
                'name'                      => 'Workshop types',
                'singular_name'             => 'Workshop type',
                'search_items'              => 'Search workshop types',
                'all_items'                 => 'All workshop types',
                'parent_item'               => 'Parent workshop type',
                'edit_item'                 => 'Edit workshop type',
                'update_item'               => 'Update workshop type',
                'add_new_item'              => 'Add new workshop type',
                'new_item_name'             => 'New workshop type',
                'choose_from_most_used'     => 'Choose from most used workshop types'
            );

            $args = array(
                'hierarchical'      => true,
                'labels'            => $labels,
                'show_admin_column' => true,
                'rewrite'           => array( 'slug' => 'event-type' ),
                'capabilities'      => array (
                    /* Only let Andrea assign workshop types */
                    'manage_terms'  => 'manage_options', // by default only admin
                    'edit_terms'    => 'manage_options',
                    'delete_terms'  => 'manage_options',
                    'assign_terms'  => 'edit_posts'  // means administrator', 'editor', 'author', 'contributor'
                )
            );

            register_taxonomy( 'lktypes', 'lkfestival', $args );
        }

        public static function register_day_taxonomy()
        {
            $labels = array(
                'name'                      => 'Festival days',
                'singular_name'             => 'Festival day',
                'search_items'              => 'Search festival days',
                'all_items'                 => 'All festival days',
                'parent_item'               => 'Parent festival day',
                'edit_item'                 => 'Edit festival day',
                'update_item'               => 'Update festival day',
                'add_new_item'              => 'Add new festival day',
                'new_item_name'             => 'New festival day',
                'choose_from_most_used'     => 'Choose from most used festival days'
            );

            $args = array(
                'hierarchical'      => true,
                'labels'            => $labels,
                'show_admin_column' => true,
                'rewrite'           => array( 'slug' => 'day' ),
                'capabilities'      => array (
                    /* Only let Andrea assign festival days */
                    'manage_terms'  => 'manage_options', // by default only admin
                    'edit_terms'    => 'manage_options',
                    'delete_terms'  => 'manage_options',
                    'assign_terms'  => 'edit_posts'  // means administrator', 'editor', 'author', 'contributor'
                )
            );

            register_taxonomy( 'lkdays', 'lkfestival', $args );
        }

    }

    // Load Class
    OSUFestivals::init();

I thought the 'sort' parameter in register_taxonomy() would allow sorting, but it looks like that's related to the order in which terms are added to objects.

Answer 1

Here is what I do to sort my custom post type by custom taxonomy in the admin. I have a custom post type called plugin_filter and a custom taxonomy assigned to it called filter_group. I add the following filter to my plugin to make the field sortable.

add_filter("manage_edit-plugin_filter_sortable_columns", 'plugin_filter_sort');
function plugin_filter_sort($columns) {
   $custom = array(
       'taxonomy-filter_group' => 'taxonomy-filter_group'
   );
   return wp_parse_args($custom, $columns);
}

Answer 2

It took me a while to find the solution for this. You need to follow the instructions given by @scribu on http://scribu.net/wordpress/sortable-taxonomy-columns.html

I´m pasting it here in case the link goes down. I´m using the third example and it works great.

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