Adding and Sorting Custom Columns

In the world of WordPress development, customizing the admin experience to fit specific needs is a common task. One such customization is adding sortable columns to admin tables. This feature is particularly useful for site administrators and content managers who need to sort data efficiently. In this post, I will guide you through adding a sortable column and modifying the query to sort its values, focusing on a practical example related to featured images.

The Need for Custom Sortable Columns

WordPress admin tables, by default, offer a limited set of sortable columns. However, for enhanced content management, especially in sites with extensive data, having the ability to sort by custom criteria (like the presence of a featured image) can be invaluable.

Adding a Sortable Column

First, we’ll add a new sortable column. The function manage_sortable_columns achieves this:

function manage_sortable_columns( $columns ) {
    $columns['uuid'] = 'uuid'; // 'uuid' is an example slug for our new column

    return $columns;
}

In this function:

• We’re adding a new column with the slug ‘fip’ to the existing columns.
• This column will be used to sort posts based on whether they have a featured image.

Customizing the Query for Sorting

Next, we need to customize the query to sort the added column’s values:

function sortable_columns_query( $query ) {
    if ( ! is_admin() ) {
        return;
    }

    // Check if it's the correct post type
    if ( ! empty( $_REQUEST['post_type'] ) && in_array( $_REQUEST['post_type'], $this->types_supported, true ) ) {
        $orderby = $query->get( 'orderby' ) ? $query->get( 'orderby' ) : '';

        switch ( $orderby ) {
            case 'uuid':
                $query->set( 'meta_key', '_thumbnail_id' ); // Sort by featured image ID
                $query->set( 'orderby', array( 'meta_value' => 'DESC', 'ID' => 'DESC' ) );
                add_filter( 'get_meta_sql', array( $this, 'filter_get_meta_sql' ) );
                break;
            default:
                break;
        }
    }
}

In this function:

• We modify the main query when sorting by our custom column.
• We set the meta key to ‘_thumbnail_id’ to sort by the presence of a featured image.

Modifying SQL for Posts without Featured Images

By default, sorting by a meta key would exclude posts without that key. To include these posts, we modify the SQL:

function filter_get_meta_sql( $clauses ) {
    remove_filter( 'get_meta_sql', 'filter_get_meta_sql' );

    // Change INNER JOIN to LEFT JOIN and adjust the WHERE clause
    $clauses['join']  = str_replace( 'INNER JOIN', 'LEFT JOIN', $clauses['join'] ) . $clauses['where'];
    $clauses['where'] = '';

    return $clauses;
}

Here, we modify the SQL join type to include all posts, even those without a featured image.

Adding custom sortable columns to WordPress admin tables, and adjusting the query for sorting, can greatly enhance the content management process. This capability allows site administrators to sort and organize data more efficiently, leading to a more streamlined admin experience.

Remember, when modifying admin tables and queries, it’s important to ensure that your changes align with the needs of your site and do not negatively impact performance or usability.