1

enter image description here

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.

0

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'";
  • This does not work because, on the Product admin screen, $post_type is 'eproduct', not 'brand-listing' like you have in your if statement (you actually have 'brand'). The full SQL query right now is: SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'eproduct' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_title ASC LIMIT 0, 20 – LeonardShelby Oct 10 '15 at 15:54
  • Replace brand with eproduct then? (In my answer) – bestprogrammerintheworld Oct 10 '15 at 17:12
  • I think now you're starting to see the problem I was trying to describe. It's tricky to explain. The post_type of the page is already "eproduct". Setting if ($post_type == 'eproduct') is redundant. It's the Product admin screen, so the post_type is already 'eproduct'. What I'm trying to do is sort by the title of a particular column on that page that has a post_type of "brand-listing". – LeonardShelby Oct 10 '15 at 17:18
  • Im not sure I do understand. Are you able to upload an image of what you are trying to do? – bestprogrammerintheworld Oct 10 '15 at 17:23
  • Yea. I uploaded an image. You can see at the top it says "Product Listings". That's the Product custom post type admin screen. You can see there is a column named "Brand". It's a custom column I added and registered to be sortable. It displays the name (post title) of the Brand associated with the Product. The Brand column is already displaying what it needs to display, and is already registered as sortable. Now I just need to define the right sort function. The sort function should sort the Products admin page by the "Brand" column. Does that help any? – LeonardShelby Oct 10 '15 at 17:30

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.