List Post Title by Last Word Then Second Last

Question

I have a CPT - Author where the title of the post is used as Author's name. I'm trying to list the authors alphabetically based on the last word (last name) and it works best.

Here is the code in the functions.php

add_filter( 'posts_orderby', function( $orderby, \WP_Query $q )
{
if( 'wpse_last_word' === $q->get( 'orderby' ) && $get_order =  $q->get( 'order' ) )
{
    if( in_array( strtoupper( $get_order ), ['ASC', 'DESC'] ) )
    {
        global $wpdb;
        $orderby = " SUBSTRING_INDEX( {$wpdb->posts}.post_title, ' ', -1 ) " . $get_order;
    }
}
return $orderby;
}, PHP_INT_MAX, 2 ); 

And to sort them, I use below code just before the query

add_filter( 'posts_orderby' , 'posts_orderby_lastname' );

My problem is when two Authors have the same last name. I want to still list them alphabetically on the second last word or the 3rd last word if the last/second last word is the same. E.g.

James Lucas
George Lucas

It currently lists the Authors based on the published date in case the last names are same.

How to list based on second last or third last word?

Answer 1

I think I got it!!!

Try this:

 $orderby = " SUBSTRING_INDEX( {$wpdb->posts}.post_title, ' ', -1 ) " . $get_order. " , {$wpdb->posts}.post_title " . $get_order;

Basically it's sorting by lastname and then first name in 1 query.

So, your whole code would be:

add_filter( 'posts_orderby', function( $orderby, \WP_Query $q )
{
if( 'wpse_last_word' === $q->get( 'orderby' ) && $get_order =  $q->get( 'order' ) )
{
    if( in_array( strtoupper( $get_order ), ['ASC', 'DESC'] ) )
    {
        global $wpdb;

       $orderby = " SUBSTRING_INDEX( {$wpdb->posts}.post_title, ' ', -1 ) " . $get_order. " , {$wpdb->posts}.post_title " . $get_order;
    }
}
return $orderby;
}, PHP_INT_MAX, 2 );