Sort custom post types by last name in the backend

Question

I have a custom post type called speakers, where post title is a speakers first- AND last name. I created it with this code:

add_action( 'init', 'create_post_type' );
function create_post_type() {
  register_post_type( 'speaker',
    array(
      'labels' => array(
        'name' => __( 'Speakers' ),
        'singular_name' => __( 'Speaker' )
      ),
      'public' => true,
      'has_archive' => true,
    'supports' => array('title','editor','thumbnail'),
    )
  );
}

I was able to successfully sort these in my wp_query with help from @birgire by adding this to my functions.php:

function posts_orderby_lastname ($orderby_statement) 
{
  $orderby_statement = "RIGHT(post_title, LOCATE(' ', REVERSE(post_title)) - 1) ASC";
    return $orderby_statement;
}

and then adding the filter before my query.

The question I have is how can I make a the posts sorted by the last name in the post list in the backend columns. I was told i need to use the pre_get_posts hook.

I tried this:

function set_custom_post_types_admin_order($wp_query) {
    if (is_admin()) {

        // Get the post type from the query
        $post_type = $wp_query->query['post_type'];

        if ( $post_type == 'speaker') {
            if (!isset($_GET['orderby'])) {
                // 'orderby' value can be any column name
                $wp_query->set('orderby', 'title');

                // 'order' value can be ASC or DESC
                $wp_query->set('order', 'ASC');
            }
        }
    }
}
add_filter('pre_get_posts', 'set_custom_post_types_admin_order');

which sorts by the post type instead of creation date but I don't know how it could sort by last name...

Answer 1

If you want to override the default- and the title ordering, and order instead by the last word in the title.

/**
 * Override default- and title ordering in the backend, for the 'speaker' custom post type.
 *
 * @link https://wordpress.stackexchange.com/a/202154/26350
 */
add_action( 'pre_get_posts', function( \WP_Query $q ) 
{
    if (  
            is_admin() 
         && $q->is_main_query() 
         && 'edit-speaker' === get_current_screen()->id 
         && ( '' === $q->get( 'orderby' ) || 'title' === $q->get( 'orderby' ) )
    ) {
        $q->set( 'orderby', 'wpse_last_word' );     
        $q->set( 'order', '' !== $q->get( 'order' ) ? $q->get( 'order' ) : 'ASC' );     
    }
} );

where we use the plugin from our last answer to support the wpse_last_word ordering.