1

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?

0

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;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.