1

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.

1 Answer 1

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']

1
  • Hi Andrea, thanks for the support, looks like this could be a solution, I am using Neve Theme and I am well verse with it, so I will handle the search.php... I have 2 worries, first, I think it will destiry the pagination, because if I have say 20 posts per page, then regardless of page number I would be returning the same post IDs... Correct me if I spoke something foolish. Secondly, your code as far as I understand defines which all posts are shown in search results, but not there order. I don't think the post__in will overrule the default order of posts set in the query.... Commented Oct 28, 2021 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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