Support

Account

Home Forums General Issues Ordering by custom field of relationship field object

Solved

Ordering by custom field of relationship field object

  • Hi, I’ve read through many of the forum posts and tutorials about querying relationship fields and still can’t get this to work. I have 2 CPTs ‘Artworks’ and ‘Artists’. Each Artwork has a relationship field to a single Artist called ‘artists_name’. Each Artist has custom fields ‘artist_first_name’ and ‘artist_last_name’ (and the title is automatically created from ‘artist_first_name’ + ‘artist_last_name’).

    I’m trying to order the Artworks archive page alphabetically by Artist last name then first name then by Artwork title. Right now it orders by the post ID of the Artist with this code:

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

    I have a filter:

    add_filter('acf/fields/relationship/result/name=artists_name', 'my_relationship_result', 10, 4);
    function my_relationship_result( $result, $post, $field, $post_id ) {
       $lname = get_field('artist_last_name', $post->ID);
       $fname = get_field('artist_first_name', $post->ID);
       $result =  $lname.', '.$fname;  
       return $result;
    }

    but the Artworks archive page still is ordered by the artist post ID. Any suggestions? Is it not possible to order the Artworks by the custom field of its related Artist?

  • Meanwhile, I’ve converted the Relationship field to a Post Object field since each Artwork only has one Artist associated with it. That has this post object query filter:

    function artist_post_object_query( $args, $field, $post_id ) {
    $args['meta_query'] = array(
        'relation' => 'AND',
        'last_name' => array(
            'key' => 'artist_last_name'
    	),
         'first_name' => array(
    	'key' => 'artist_first_name'
          ));
          $args['orderby']	= array(
    	'last_name' => 'ASC',
    	'first_name' => 'ASC'
    	);	
        // return
        return $args;
    }
    add_filter('acf/fields/post_object/query/name=artists_name', 'artist_post_object_query', 10, 3);

    So Artist correctly alphabetized in the Artwork edit screen. (Although that filter doesn’t extend to the List screen sort order, for which I’m using Admin Columns Pro.)

    Essentially I want that filter to work any time the Artworks field ‘artist_name’ is being used as a meta_key to sort so that ‘meta_value’ can be replaced with ‘artist_last_name’ + ‘artist_first_name’ instead of the post ID. It seems like a posts_join and then posts_orderby filter would work but I can’t get this join to work:

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

    Any help would be much appreciated.

  • You cannot do this. You are attempting to order “Post Type A” by a values associated with “Post Type B”. In order to order “Post Type A” by these values they need to be saved as custom fields for “Post Type A”.

    This is possible, but to do it you need to create an acf/save_post filter. In this filter you would get the values from the related post and save them as custom field values for the artwork post type.

  • Thanks, @hube2. I was afraid of that. I do have the values from “Post Type B” loading into fields in a few posts of “Post Type A” using acf/load_value but would then have to go back and update all posts in “Post Type A” to get those values loading in the database. Thought I would exhaust the posts_join option before doing that since it seemed similar in concept to the acf/post-object/query filter. (And is there a way to bulk update all the posts of “Post Type A”?)

  • I missed the post join part, and going to be honest, I don’t know how to do that. I try to avoid anything that is likely to slow a site and adding extra joins in db queries can really slow down a WP site.

    There isn’t an easy way to update all of the existing posts. Basically, you need to do a query, get all the posts, loop though them, get the related post and update the values. If you have a lot of posts on your site doing this will likely just timeout the browser trying to do it.

  • Also, another problem with duplicating field values into Post Type A from Post Type B: those fields in Post Type A won’t reflect any changes made to that field in Post Type B. (E.g. I can load ‘last_name’ from Post Type B when creating or updating Post Type A; but it won’t dynamically update if Post Type B ever updates that field.) That’s why the posts_join seems like it might be the solution without slowing down the site too much.

  • When I say duplicate fields I don’t necessarily mean that I’d create an ACF field on both field types that match. What I mean is that I would create a custom field using the standard WP functions like add_post_meta() with a different field name. The only purpose for this field name is to have a value to search by in the database. I outlined a similar idea about repeater fields here https://acfextras.com/dont-query-repeaters/

  • Read that post. Unless I’m missing something, that would still only save a wp field value when Post Type A is saved/updated and that value wouldn’t be dynamically updated if ever it was changed in Post Type B?

  • The posts_join worked. Posting code below in case it helps anyone else.

    $args = array(
    	'post_type'         => 'artworks',
    	'posts_per_page'    => -1,
    	'no_found_rows' 	=> true,
    	'order'				=> 'ASC',
    	'orderby'			=> 'artist_name_artwork_title',
    );
    
    add_filter('posts_join', 'test_join', 10, 2 );
    function test_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;
    }
    
    add_filter('posts_orderby', 'orderby_artist_name_artwork_title', 10, 2);
    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;
    }
    
    $wp_query = new WP_Query( $args );
    		
    remove_filter('posts_join', 'test_join', 10 );
    remove_filter('posts_orderby', 'orderby_artist_name_artwork_title', 10 );
  • Sorry I didn’t get back to you sooner, I’ve had a crazy week. Glad you got it worked out.

    Your last question was correct, it would not be updated if the related post is updated. I would probably go with some alternate approach still other than using those joins because of site speed…

    …though I don’t know how I would do that. When the post is updated at the other end I would probably do something like doing a query to get all the posts that have the relationship and update all of the values for those posts. This would slow down the admin when saving posts but that to me is preferable to slowing down the front end of the site.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.

We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Cookie Policy. If you continue to use this site, you consent to our use of cookies.