CODEWITHSUNDEEP
mysqlwordpresswoocommerce
Martin
Martin

Reputation: 795

MYSQL order by query

If I have custom post types (post_type), and I want to guarantee that anything with the post type of product appears before anything else in the search result, how could I achieve this? Preferably by just altering the order by part of the query.

Currently I have:

 ORDER BY posts.post_type DESC, posts.post_date DESC

This works, except another post type of testimonial comes before product on DESC. On ASC a different custom post type articles comes before product but I NEED product to come first (with them ordered by post_date ), then everything else - also ordered by post_date.

The full code:

add_filter('posts_orderby','search_sort_custom',10,2);
function search_sort_custom( $orderby, $query )
{
    global $wpdb;

    if(!is_admin() && is_search()) {
        $orderby =  $wpdb->prefix."posts.post_type DESC, {$wpdb->prefix}posts.post_date DESC";

    }
   return  $orderby;
}

Upvotes: 0

Views: 94

Answers (2)

FJam
FJam

Reputation: 756

You can simply just do

ORDER BY (
     posts.post_type = 'product' DESC, 
     posts.post_type DESC,
     posts.post_date DESC
)

Upvotes: 0

Arth
Arth

Reputation: 13110

You can use expressions in an ORDER BY clause:

ORDER BY posts.post_type='product' DESC,
         posts.post_type DESC,
         posts.post_date DESC

If you have more requirements you can do:

ORDER BY posts.post_type='product' DESC,
         posts.post_type='testimonial' DESC,
         posts.post_type DESC,
         posts.post_date DESC

or use FIND_IN_SET:

ORDER BY FIND_IN_SET(posts.post_type,'product, testimonial'), 
         posts.post_type DESC,
         posts.post_date DESC

Ideally however I would shift the product types to another table and give each type a priority integer.. that way you can fully control the order without such dramas!

Upvotes: 1

Related Questions