Order a custom post type admin screen by a second custom post type title

Question

I have two custom post types: Products and Brands. The Product slug is 'eproduct'. The Brand slug is 'brand-listing'. On the Product admin page, I have added a custom column that lists the Brand name. I am trying to make the "Brand" custom column sortable by the brand title (post_title of the Brand custom post type associated with that product). This is the code I have so far, using the posts_clauses filter.

add_filter('posts_clauses', 'vendia_sort_brand_column',10,2);
function vendia_sort_brand_column( $clauses, $query ) {
global $wpdb;

if ( ! $query->is_main_query()
    || ! is_admin()
    || ! $query->get('post_type') == 'eproduct'
){
    return $clauses;
}

$clauses['orderby'] = " {$wpdb->posts}.post_title ";

return $clauses;
}

As shown above, I have tried to modify the orderby parameter of the SQL query, using the post_clauses filter.

$clauses['orderby'] = " {$wpdb->posts}.post_title";

My stumbling block is that both the "Brand" and "Product" custom post types are in the wp_posts table. So {$wpdb->posts}.post_title returns the Product title, not the Brand title that I am trying to order by. Thus, my Product admin page is simply sorted by Product title, not Brand title.

What I want to do is something like this

$clauses['orderby'] = " {$wpdb->posts}.post_title WHERE {$wpdb->posts}.post_type = 'brand-listing'";

The key change is that I have added WHERE {$wpdb->posts}.post_type = 'brand-listing' to the ORDERBY clause. However, apparently that is not valid SQL. My query returns 0 posts, and thus my Products admin screen lists no posts. I guess a WHERE clause is not allowed inside an ORDERBY clause? Or is it?

I am a novice to SQL, so I am wondering, what would be the valid SQL ORDERBY clause to accomplish what I am trying to do?

I thank anyone who replies, for any help. I have been working on this for light years.

Answer 1

It seems like your mixing slug and post-type. Slug is used to identify a page/post when some entering it into the browser (very related to a permalink). A post type is used to identify what kind of post that is stored in the wordpress database.

post_clauses is used if you want to change several things in a query at once. I would recommend using pre_get_posts instead (if you don't want to do more complex queries than ordering)

Based on what you've wrote:

$clauses['orderby'] = " {$wpdb->posts}.post_title WHERE {$wpdb->posts}.post_type = 'brand-listing'";

it seems like you only want orderby posts that custom post type brand (not brand-listing that seems to be the slug?)

/* Sort posts in wplist admin */
function custom_post_order($query){
    if ( ! $query->is_main_query() || ! is_admin() {return;}

    $post_type = $query->get('post_type');

    if ($post_type == 'brand') {            
        $query->set( 'orderby', array('title' => 'ASC') );
    }

}
add_action('pre_get_posts', 'custom_post_order');

This won't work...

$clauses['orderby'] = " {$wpdb->posts}.post_title WHERE {$wpdb->posts}.post_type = 'brand-listing'";

...because Wordpress uses the orderby-argument here to include orderby only.

(I guess WP would create a query something like this:

order by wp_posts WHERE wp_posts.post_type = 'brand-listing'
where wp_posts_type = 'post' //default created by wordpress

and therefore sql is incorrect (two where clauses) )

If you still want to use post_clauses you should be able to use this:

$clauses['orderby'] = "{$wpdb->posts}.post_title";
$clauses['where'] = "{$wpdb->posts}.post_type = 'brand'";