Custom Table Column Sortable by Taxonomy Query

I am looking to add a custom column to a custom post type post listing table in the dashboard.

I have read many questions / answers on WPSE, along with this article. Although, it seems like everyone wants to query by meta key / value.

I am trying to add a taxonomy value (basically a category) to a custom post type table.

I would like the column to be sortable, however I am not understanding the query adjustment.

Add the column title. 

function mbe_column_titles($columns){
    $columns['title'] = 'Frequently Asked Question';
    $columns['mbe-faq-category'] = 'Category';
    return $columns;
}
add_filter('manage_mbe-faqs_posts_columns', 'mbe_column_titles');

Add the row values.

function mbe_column_rows($column_name, $post_id){
    if($column_name == 'mbe-faq-category'){
        $categories = wp_get_object_terms($post_id, 'mbe-faq-categories');
        $the_category = array();
        if($categories){
            foreach($categories as $category){
                $the_category[] = $category-name;
            }
        }
        echo join(', ', $the_category);
    }
}
add_action('manage_mbe-faqs_posts_custom_column', 'mbe_column_rows', 10, 2);

Prepare the ordering key,

function mbe_sortable_columns($columns){
    $columns['mbe-faq-category'] = 'mbe-faq-category';
    return $columns;
}
add_filter('manage_edit-mbe-faqs_sortable_columns', 'mbe_sortable_columns');

I am stumped on how to actually sort the column by the FAQ Category. I would assume this would be a taxonomy query. I am too embarrassed to even post my attempt of handling this, and everything I do seems to fail. I would like to use the pre_get_posts filter to handle this if possible.

Topic screen-columns wp-list-table pre-get-posts dashboard sort Wordpress

Category Web

1
answered at

This can be done a lot easier with a single filter:

/**
 * Sorting by Taxonomy Term
 *
 * Add column sorting for a custom taxonomy to a custom post type edit view in WordPress.
 *
 * Note: Replace the {post_type} and {taxonomy} values below.
 */
add_filter( 'manage_edit-{post_type}_sortable_columns', function( $sortable_columns ) {
  $sortable[ 'taxonomy-{taxonomy}' ] = ['orderby', 'asc'];
  return $sortable;
}, 10, 1 );

Replace {post_type} and {taxonomy} with the post type and taxonomy you're working with. The end result will be a custom column on your custom post type that is sortable, and can be used to sort the posts in the WP_List_Table.

1
answered at

To achieve adding a custom sortable column to the WP_List_Table of your post type within the WordPress administration back-end dashboard, you will need to do the following:

  1. Replace all occurrences of YOUR-POST-TYPE-NAME with your actual post type name.
  2. Replace all occurrences of YOUR-TAXONOMY-NAME with your actual taxonomy name.
  3. Replace all occurrences of YOUR COLUMN NAME with your actual column name.
  4. Replace all occurrences of YOUR-COLUMN-SLUG with your actual column slug.

Step 1

Add Additional WordPress Admin Table Columns

if(!function_exists('mbe_change_table_column_titles')){
    function mbe_change_table_column_titles($columns){
        unset($columns['date']);// temporarily remove, to have custom column before date column
        $columns['YOUR-COLUMN-SLUG'] = 'YOUR COLUMN NAME';
        $columns['date'] = 'Date';// readd the date column
        return $columns;
    }
    add_filter('manage_YOUR-POST-TYPE-NAME_posts_columns', 'mbe_change_table_column_titles');
}

Step 2

Add All Assigned Linkable Taxonomy Terms as Row Data Within Custom WordPress Admin Table Column

if(!function_exists('mbe_change_column_rows')){
    function mbe_change_column_rows($column_name, $post_id){
        if($column_name == 'YOUR-COLUMN-SLUG'){
            echo get_the_term_list($post_id, 'YOUR-TAXONOMY-NAME', '', ', ', '').PHP_EOL;
        }
    }
    add_action('manage_YOUR-POST-TYPE-NAME_posts_custom_column', 'mbe_change_column_rows', 10, 2);
}

Step 3

Enable Custom WordPress Admin Table Column to Become Sortable

if(!function_exists('mbe_change_sortable_columns')){
    function mbe_change_sortable_columns($columns){
        $columns['YOUR-COLUMN-SLUG'] = 'YOUR-COLUMN-SLUG';
        return $columns;
    }
    add_filter('manage_edit-YOUR-POST-TYPE-NAME_sortable_columns', 'mbe_change_sortable_columns');
}

Step 4

Modify post_clauses to Allow Sorting Custom WordPress Admin Table Columns by a Taxonomy Term

if(!function_exists('mbe_sort_custom_column')){
    function mbe_sort_custom_column($clauses, $wp_query){
        global $wpdb;
        if(isset($wp_query->query['orderby']) && $wp_query->query['orderby'] == 'YOUR-COLUMN-SLUG'){
            $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 = 'YOUR-TAXONOMY-NAME' OR taxonomy IS NULL)";
            $clauses['groupby'] = "object_id";
            $clauses['orderby'] = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC)";
            if(strtoupper($wp_query->get('order')) == 'ASC'){
                $clauses['orderby'] .= 'ASC';
            } else{
                $clauses['orderby'] .= 'DESC';
            }
        }
        return $clauses;
    }
    add_filter('posts_clauses', 'mbe_sort_custom_column', 10, 2);
}

Step 5 (BONUS)

Adjust the Width of Custom WordPress Admin Table Columns

if(!function_exists('mbe_print_admin_styles')){
    function mbe_print_admin_styles(){
        if(!is_admin()){
            return false;
        }
        global $pagenow;
        if($pagenow == 'edit.php' && isset($_GET['post_type']) && $_GET['post_type'] == 'YOUR-POST-TYPE-NAME'){
            echo '
        <style type="text/css">
            .column-YOUR-COLUMN-SLUG{
                width: 10%;
            }
        </style>
        '.PHP_EOL;
        }
    }
    add_action('wp_print_scripts', 'mbe_print_admin_styles');
}

Thanks to @goto10 for asking Sortable admin columns, when data isn't coming from post_meta and @scribu for posting Custom Sortable Columns and Sortable Taxonomy Columns for this answer to the original question Custom Table Column Sortable by Taxonomy Query.

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.