Ordering Posts Type A by Custom Fields of related Post Type B

Question

I'm not sure if this is even possible (also posted to ACF forum https://support.advancedcustomfields.com/forums/topic/ordering-by-custom-field-of-relationship-field-object/#post-62454)

I have two custom post types, Artworks and Artists. Artworks has an ACF post object field 'artists_name' relating it to a single Artist. Artists have custom fields 'artist_last_name' and 'artist_first_name'. On the Artworks archive page, posts are ordered by meta_value of the meta_key 'artists_name' but of course it orders it by post ID.

I'm trying to get the query to orderby the related artist's last name then first name. (I.e. I want to use meta value from Post Type B to sort Post Type A.)

So far I have this query, which works as expected:

$args = array(
    'post_type'         => 'artworks',
    'posts_per_page'    => -1,
    'meta_key'          => 'artists_name',
    'orderby'           => 'meta_value'
);

Then I'm trying to get a posts_join filter to find post meta whose post ID = the meta_value of 'artists_name'

add_filter('posts_join', 'trial_join', 10, 2 );
function trial_join($joins) {
    if(is_post_type_archive('artworks') || (is_admin() && $_GET['post_type'] == 'artworks')) {
    global $wpdb;        
    $joins .= "  INNER JOIN {$wpdb->postmeta} artists_name ON artists_name.post_id={$wpdb->posts}.ID WHERE artists_name.meta_key='artist_last_name'" ;
    $joins .= "  INNER JOIN {$wpdb->postmeta} first_name ON first_name.post_id={$wpdb->posts}.ID WHERE (first_name.meta_key='artist_first_name' AND first_name.post_ID=artists_name.meta_value)";
    $joins .= "  INNER JOIN {$wpdb->postmeta} last_name ON last_name.post_id={$wpdb->posts}.ID WHERE (last_name.meta_key='artist_last_name' AND last_name.post_ID=artists_name.meta_value)";
    }
    return $joins;
}

And then use a posts_orderby filter:

add_filter('posts_orderby', 'orderby_pages_callback', 10, 2);
function orderby_pages_callback($orderby_statement, $wp_query) {
    $orderby_statement = "last_name.meta_value first_name.meta_value ASC";
    return $orderby_statement;
}

But it's not working. I've cobbled together the join from a bunch of other posts. Any suggestions?

Answer 1

This works. The filters are tied to a custom 'orderby' term and removed after the query.

$args = array(
    'post_type'         => 'artworks',
    'posts_per_page'    => -1,
    'no_found_rows'     => true,
    'order'             => 'ASC',
    'orderby'           => 'artist_name_artwork_title',
);

add_filter('posts_join', 'name_join', 10, 2 );
add_filter('posts_orderby', 'orderby_artist_name_artwork_title', 10, 2);

// query
$wp_query = new WP_Query( $args );
remove_filter('posts_join', 'name_join', 10 );
remove_filter('posts_orderby', 'orderby_artist_name_artwork_title', 10 );

function name_join($joins, $wp_query) {
    if ( $wp_query->get( 'orderby' ) != 'artist_name_artwork_title' ) {
        return $joins;
    }   
    global $wpdb;        
    $joins .= "  LEFT JOIN $wpdb->postmeta name ON name.post_id=$wpdb->posts.ID AND name.meta_key='artists_name'" ;
    $joins .= "  LEFT JOIN $wpdb->postmeta lastname ON lastname.post_id=name.meta_value AND lastname.meta_key='artist_last_name'";
    $joins .= "  LEFT JOIN $wpdb->postmeta firstname ON firstname.post_id=name.meta_value AND firstname.meta_key='artist_first_name'";
    return $joins;
}

function orderby_artist_name_artwork_title($orderby_statement, $wp_query) {
    if ( $wp_query->get( 'orderby' ) != 'artist_name_artwork_title' ) {
        return $orderby_statement;
    }   
    global $wpdb;
    $orderby_statement = "lastname.meta_value, firstname.meta_value, $wpdb->posts.post_title";
    return $orderby_statement;
}