Order by the results of a function with WP_query

Question

I am attempting to order the results from WP_query by a custom value. Specifically, I want to use the name field, however, the name field is going to be made up of many words separated by spaces. I specifically want to order by the last word in the name field.

Currently, my query args are this:

$args = array(
    'post_parent'    => $post->ID,
    'order'          => 'ASC',
    'orderby'        => 'name',
    'post_type'      => 'page',
    'posts_per_page' => 8,
    'paged'          => get_query_var( 'paged' ),
    'meta_key'       => 'team_school',
    'meta_value'     => $school_type
);

I have seen information on the meta_query argument but nothing that indicates I can do what I am looking to do.

Answer 1

@JasCav Here is something to put you in the right direction. Maybe someone can edit where needed.

// args  
$args = [
   'post_parent'    => $post->ID,
   'post_type'      => 'page',
   'posts_per_page' => 8,
   'paged'          => get_query_var( 'paged' ),
   'meta_key'       => 'team_school',
   'meta_value'     => $school_type,
   'orderby'        => 'wpse_last_word', //<-- make filter for ordering!
   'order'          => 'ASC'
];

// query
$the_query = new WP_Query( $args );


     add_filter( 'posts_orderby', function( $orderby, \WP_Query $q )
     {

       if( isset( $args['orderby'] ) && 'wpse_last_word' === $args['orderby'] )
       { 

       global $wpdb;       
       $orderby = " SUBSTRING_INDEX(  $wpdb->postmeta.meta_value, ' ', -1 ) "; 

       }

       return $orderby;

     }, PHP_INT_MAX, 2 );

Referenced from https://wordpress.stackexchange.com/a/198624/29133