[Resolved] Sorting custom posts based on the names of another custom post.

[Author]

Posts

[josephC-5]

Hi there,

I'm building a site for an art gallery. The site has two custom posts: Artist and Artwork, and they are connected to each other via a one-to-many relationship. On the Artwork admin screen, I added a custom column named "Artist" so I can see which artist is connected to a given artwork.

What I would like to do is be able to click the "Artist" column heading to sort the artworks based on the last name of the Artist (Last Name is a custom field).

Is this possible to do? I know I need to hook into pre_get_posts(), but that's where I'm not sure how to proceed.

function cas_posts_orderby ( $query ) {
	if( ! is_admin() || ! $query->is_main_query() ) {
		return;
	}

	if ( $query->get( 'orderby') === 'artwork_artist' ) {
//		magic happens here!
	}
}
add_action ( 'pre_get_posts', 'cas_posts_orderby' );

Thank you!

Saul

[Minesh]

Hello. Thank you for contacting the Toolset support.

There is no such feature exist to add parent post column to your child post admin screen and add sorting to that column. To add such column you need to add/use few hooks which needs custom programming which is beyond the scope of our support policy.

However, I'm sharing few related links that may help you:
=> https://codex.wordpress.org/Plugin_API/Action_Reference/manage_posts_custom_column
=> hidden link
=> https://code.tutsplus.com/articles/quick-tip-make-your-custom-column-sortable--wp-25095
=> https://wordpress.stackexchange.com/questions/293318/make-custom-column-sortable
=> hidden link

[josephC-5]

Minesh,

Thanks for your response. I figured it was completely outside the scope of Toolset support but wanted to start with you guys since you're WordPress wizards and might have some pointers—which you did! Thank you for sharing the links.

I ended up using none of those resources nor my snippet of code but did manage to find a solution on my own. My query was too complex; I joined the wp_postmeta table twice to wp_posts and needed to create table aliases. Just for posterity, in case someone else goes looking for something similar in the future, here is my solution.

The Problem. In the admin screen, I want to sort post type A based on custom fields in post type B. I had two custom post types: Artist and Artwork. They were connected via a one-to-many relationship. On the listing of Artworks, I wanted a sortable field name, "Artist," displaying the first name and last name (which are custom fields on the Artist post) of the artwork's artist in the format "last name, first name."

The Query. Below is the SQL query.

select artworks.post_title as artwork, concat(wpm1.meta_value,', ',wpm2.meta_value) as artist_name from wp_posts as artworks, wp_posts as artists, wp_toolset_associations as wta, wp_postmeta as wpm1, wp_postmeta as wpm2 where artworks.id = wta.child_id and artists.id = wta.parent_id and artists.id = wpm1.post_id and artists.id = wpm2.post_id and wpm1.meta_key='wpcf-last-name' and wpm2.meta_key='wpcf-first-name' and artworks.post_type='artwork' and artists.post_type='artist' order by wpm1.meta_value asc, wpm2.meta_value asc ;

Note: wpm1.meta_value is the surname, wpm2.meta_value is the first name.

The Solution. Because I needed to adjust several clauses in the query and change the name of the table I was selecting data from, I used the posts_clauses() hook to tweak the FIELDS, WHERE, and ORDER BY clauses, then I used the posts_request() hook to replace the table name, "artworks," with "wp_posts as artworks, wp_posts as artists, wp_toolset_associations as wta, wp_postmeta as wpm1, wp_postmeta as wpm2".

Caveat: don't forget to incorporate post_status in the posts_clauses() code! Otherwise filters won't work.

Hope this helps!

Saul