Re-order search results with posts_orderby filter and post meta value

Question

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;
    }

Answer 1

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');

Answer 2

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;
    }