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.