Make a custom column sortable by a value from a different custom post type

Question

I've created some custom columns to display my custom post types more attractively in the admin section - the columns are mostly custom fields. Mostly, this works absolutely fine, and I can sort by the custom field columns as expected.

However, one of the custom fields points to the post ID of a different post type. When displaying it, it looks fine, as I can grab the title of the associated post from its ID, rather than displaying the ID itself - all this is in my code below.

I can't figure out how to replicate this in the column sort though, or even if it's possible - I can only see how to sort by the meta-value, which is the ID of the linked post, rather than the title.

My code is working technically correctly, as in it's sorting by the meta value correctly, but I want to sort it alphabetically by the post title with that ID rather than the ID itself. Can this be done, and if so, how?

add_action( 'manage_posts_custom_column' , 'custom_columns', 10, 2 );

    function custom_columns( $column, $post_id ) {
        global $wpdb;
        switch ( $column ) {
            case 'extranet_client_area': // Extranet documents
                $get_case_ID = get_post_meta( $post_id, 'extranet_client_area', true );
                $get_case_name = $wpdb->get_results('SELECT post_title FROM `cn_bf_posts` WHERE `ID` = '.$get_case_ID);
                echo '<a href="http://www.bishopfleminginsolvency.co.uk/wp-admin/post.php?post='.$get_case_ID.'&action=edit">'.$get_case_name[0]->post_title.'</a>';
                break;
            case 'extranet_document_type':
                extranet_nice_document_type(get_post_meta( $post_id, 'extranet_document_type', true ));
                break;
            case 'extranet_document_date':
                echo date('d/m/Y',strtotime(get_post_meta( $post_id, 'extranet_document_date', true ))); 
                break;
            case 'extranet_file':
                echo '<a target="_blank" href="'.get_the_guid(get_post_meta( $post_id, 'extranet_file', true )).'">Download</a>';
                break;
            default:
                break;
        }
    }

add_action( 'pre_get_posts', 'extranet_orderby' );

    function extranet_orderby( $query ) {
        if( ! is_admin() )
            return;

        $orderby = $query->get( 'orderby');
        switch ( $orderby ) {
            case 'extranet_sort_document_case':
                $query->set('meta_key','extranet_client_area');
                $query->set('orderby','meta_value');
                break;  
            case 'extranet_sort_document_type':
                $query->set('meta_key','extranet_document_type');
                $query->set('orderby','meta_value_num');
                break;
            case 'extranet_sort_document_date':
                $query->set('meta_key','extranet_document_date');
                $query->set('orderby','meta_value');
                break;
            default:
                break;
        }


add_filter('manage_clientdocument_posts_columns','extranet_document_columns');

function extranet_document_columns($columns) {

    $columns = array(
        'cb'=>'<input type="checkbox" />',
        'extranet_client_area' => __( 'Case Name' ),
        'title' => __( 'Document Name' ),
        'extranet_document_type' => __( 'Document Type' ),
        'extranet_document_date' => __( 'Document Date' ),
        'extranet_file' => __( 'Download link' )            
    );

    return $columns;

}

add_filter( 'manage_edit-clientdocument_sortable_columns', 'my_sortable_clientdocuments_columns' );

function my_sortable_clientdocuments_columns( $columns ) {
    $columns['extranet_client_area'] = 'extranet_sort_document_case';
    $columns['extranet_document_type'] = 'extranet_sort_document_type';
    $columns['extranet_document_date'] = 'extranet_sort_document_date';
    return $columns;
}

Answer 1

(I was going to add this as a comment as it's not a full answer, but I can't yet...)

I think you will need to use an SQL query as the standard meta_queries / orderby won't work. Have a look at Custom Table Column Sortable by Taxonomy Query and http://wpdreamer.com/2014/04/how-to-make-your-wordpress-admin-columns-sortable/#sorting-posts-option-b for a couple of examples.

Answer 2

Building on Phil's answer:

Made the following change to the switch:

case 'extranet_sort_document_case':
add_filter('posts_join', 'extranet_clientdocument_case_join');
add_filter('posts_fields', 'extranet_clientdocument_case_fields');
add_filter('posts_orderby', 'extranet_clientdocument_case_order');
break;  

and wrote the following functions:

function extranet_clientdocument_case_join($join) {
    global $wpdb;
    $join .= " LEFT JOIN ".$wpdb->postmeta." AS case_ids ON (".$wpdb->posts.".ID = case_ids.post_id AND case_ids.meta_key = 'extranet_client_area') 
               LEFT JOIN ".$wpdb->posts." AS case_names ON (case_names.ID = case_ids.meta_value) ";
    return $join;           
}

function extranet_clientdocument_case_fields($fields) {
    $fields.=", case_names.post_title";
    return $fields;
}

function extranet_clientdocument_case_order($order_by) {
    if(isset($_GET['order'])) $direction = $_GET['order'];
    else $direction = 'ASC';
    $order_by = 'case_names.post_title '.$direction;
    return($order_by);  
}

This worked in terms of making the column sortable by the post name of the related custom post, rather than by the metadata itself. However, it created a new problem in that the Title of the main post being listed, which is unusually the second column, now turned into the post title of the related post. I fixed this with the following additional code:

function restore_original_title($title) {
    global $wpdb;
    $post_id = get_the_ID();
    $get_document_name = $wpdb->get_results('SELECT * FROM `wp_posts` WHERE `ID` = '.$post_id);
    $title = $get_document_name[0]->post_title;
    return $title;          
}

and added the following additional filter:

add_filter( 'the_title', 'restore_original_title', 10, 2 );