Build A Custom SQL Query for WordPress Search

Question

I was working on a code, where I used SQL to find something on my site.

I realised that the SQL was giving me far accurate search results, and wanted to implement the same in my WordPress search query.

I can't figure out a way to use SQL in WordPress search.

Here's the SQL Query:

        $Query = "SELECT SQL_CALC_FOUND_ROWS *
            FROM $wpdb->posts
            WHERE 1=1
            AND $wpdb->posts.post_status IN ('draft', 'publish', 'private', 'privatised', 'future', 'inherit', 'pending')
            AND (($wpdb->posts.post_title like '%". $Search . "%') OR ($wpdb->posts.post_excerpt like '%". $Search . "%') OR ($wpdb->posts.post_content like '%". $Search . "%'))
            ORDER BY    CASE ($wpdb->posts.post_title LIKE '" . $Search . " %') and ($wpdb->posts.post_content LIKE '% " . $Search . " %') WHEN 1 THEN $wpdb->posts.post_date END DESC,
                CASE ($wpdb->posts.post_title LIKE '% " . $Search . "') and ($wpdb->posts.post_content LIKE '% " . $Search . " %') WHEN 1 THEN $wpdb->posts.post_date END DESC,
                CASE ($wpdb->posts.post_title LIKE '% " . $Search . " %') and ($wpdb->posts.post_content LIKE '% " . $Search . " %') WHEN 1 THEN $wpdb->posts.post_date END DESC,
                CASE ($wpdb->posts.post_title LIKE '% " . $Search . " %') and ($wpdb->posts.post_content LIKE '%" . $Search . "%') WHEN 1 THEN $wpdb->posts.post_date END DESC, 
                CASE ($wpdb->posts.post_title LIKE '%" . $Search . " %') and ($wpdb->posts.post_content LIKE '%" . $Search . " %') WHEN 1 THEN $wpdb->posts.post_date END DESC, 
                CASE ($wpdb->posts.post_title LIKE '%" . $Search . " %') and ($wpdb->posts.post_content LIKE '%" . $Search . "%') WHEN 1 THEN $wpdb->posts.post_date END DESC, 
                CASE ($wpdb->posts.post_title LIKE '%" . $Search . "%') and ($wpdb->posts.post_content LIKE '%" . $Search . " %') WHEN 1 THEN $wpdb->posts.post_date END DESC
            LIMIT " . ($Page  -1) *     20 . ", 20 ";

    $Creations = $wpdb->get_results($Query, OBJECT);

So, as you can see I am using lot's of lines in Order By, to have the most relevant post first.

Please suggest a way to do the same in my WordPress search.

I could find couple of existing solutions that could sort it by Title, or by Date, but not in the way I want.

Answer 1

A possible solution is to use the pre_get_posts action:

add_action('pre_get_posts', 'custom_search_query');

function custom_search_query($query) {

  // run only if is a search query and if the search string is not empty
  if($query->is_search() && $query->is_main_query() && get_query_var('s', false)) {

    // here you can use your custom query having 'get_query_var('s', false)' as $Search string
    //"SELECT SQL_CALC_FOUND_ROWS *..........
    
    // just modify your query to return only the IDS of the posts found and put them in an array $ids
    $ids=[1,2,3,4]; // example of IDS found by your custom query
    
    // unset the query_var otherwise the custom SQL will not work
    unset( $query->query_vars['s'] );

    // set the IDS as 'post__in' in the $query
    $query->set( 'post__in', $ids );
  }
}

Having removed the $query->query_vars['s'] the only missing thing will be the searched string in the template file 'search.php' where, normally, there will be something like: ( from twentytwentyone theme)

<h1 class="page-title">
<?php
printf(
    /* translators: %s: Search term. */
    esc_html__( 'Results for "%s"', 'twentytwentyone' ),
    '<span class="page-description search-term">' . esc_html( get_search_query() ) . '</span>'
    );
?>
</h1>

So get_search_query() will print an empty string instead of the searched string, just replace the get_search_query() function with $_GET['s']