php – 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.

0
Aditya Agarwal 1 month 2021-10-28T02:23:38-05:00 0 Answers 0 views 0

Leave an answer

Browse
Browse