0

I'd like to re-order the search results based on post meta and date . The post meta key is _prioritize_s and the value isn't important. So in a "normal" meta query, I'd just write the compare parameters to check EXISTS/NOT EXISTS. While I have experience with meta queries, I've never had to re-order search results until now, so I'd appreciate any help.

This is how I'd like to re-order the search results:

  • Check if posts with post meta _prioritize_s exists within search results
  • If they do, put those on top of the results in date order (from recent to oldest). After these posts are shown, show the remaining search results underneath in their default order.
  • If there are no posts with this post meta among the search results, order by whatever is default

It looks like I have to use 2 filters, one to join and one to set the order. It seems like it's kind of working, but the order isn't right? Thoughts?

Updated Code from this sample

    add_filter( 'posts_join', 'modify_search_results_join' );
    add_filter( 'posts_orderby', 'modify_search_results_order', 10, 2 );


    function modify_search_results_join( $orderby ) {
        global $wpdb;

        if ( ! is_admin() && is_search() ) {
            $orderby .= "LEFT JOIN (
            SELECT *
            FROM $wpdb->postmeta
            WHERE meta_key =  '_prioritize_s' ) AS postmeta ON $wpdb->posts.ID = postmeta.post_id";

            return $orderby;
        }

        return $orderby;
    }

    function modify_search_results_order( $orderby, $query ) {
        global $wpdb;
        //how do I order the results by _prioritize_s set to 'yes'?
        $orderby = "postmeta.meta_value+'' DESC";

        return $orderby;
    }
0

Probably it's just a JOIN problem: the SELECT query has no postmeta fields to order by. Try to implement the second answer from this question.

That is, paste this code in your file.

function custom_posts_join($join){
     global $wpdb;

     $join .= " LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id ";

     return $join;
}
add_filter( 'posts_join' , 'custom_posts_join');
  • I'm getting SQL syntax errors now. I admit that I don't have much experience writing SQL for WordPress. Can you provide an example to how the query should look? I tried $orderby = "LEFT JOIN" . $wpdb->prefix . "postmeta.meta_key = '_prioritize_s' ON, {$wpdb->prefix}posts.post_date DESC"; Thanks. – RachieVee Feb 12 at 15:04
  • have you pasted the code I posted as is in the same block you put the code you posted? – Fabrizio Mele Feb 12 at 22:06
  • I did try it, and while there are no errors now, it doesn't seem to resolve my original question. Where it puts the posts with the post meta I'm looking for on top of the search results. – RachieVee Feb 13 at 15:06
0

After much googling and brainstorming with someone else, it turns out that I may have been overcomplicating this. I was under the impression that I could not use the pre_get_posts hook when it came to search results. Turns out, I can. I used a meta query to check whether the post meta exists or doesn't exist. I also included checks to prevent the function from running if it wasn't the main query, was admin or wasn't search.

Here is what's working for me:


    add_filter( 'pre_get_posts', 'modify_search_results_order', 10, 2 );


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

        $query->query_vars['order'] = 'DESC';
        $query->query_vars['orderby']    = 'meta_value date';
        $query->query_vars['meta_query'] = [
            'relation' => 'OR',
            array(
                'key'   => '_prioritize_s',
                'compare' => 'EXISTS'
            ),
            array(
                'key'     => '_prioritize_s',
                'compare' => 'NOT EXISTS'
            )
        ];

        return $query;
    }

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.