Query Builder exact match first and then try loose matches PostgreSQL

I am using PostgreSQL.

Situation: Say I have quite a few posts with "Shoes" in the title but there is also a post where the title is only the word "Shoes".

Aim; I want to perform a database query where it shows the post with the exact match of "Shoes" first, and then all other results where the word "Shoes" can be anywhere within the title.

Attempt: To do this I made the following code:

public function findPost ($query) {
	$looseQuery = '%' . $query . '%';
	
	$suggestions = Post::where('title', $query)
						->orWhere('title', 'ilike', $looseQuery)
						->limit(5)
						->get();
	
	return $suggestions;
}

The SQL for this is:

select * from "posts" where ("title" = ? or "title"::text ilike ?) limit 5;

Issue: However, when I run this, the exact match comes at the end of the collection rather than the beginning? As a result if there are more than 5 posts satisfying the $looseQuery, then the exact match doesn't show up at all.

How can I get the exact match first?

bugsysha

5 years ago

Best Answer

Level 61

Something like this should work:

$suggestions = Post::where('title', $query)
						->orWhere('title', 'ilike', $looseQuery)
						->limit(5)
						->orderByRaw('(title = ?) DESC, LENGTH(title)', $title)
						->get();

1 like

CLab

5 years ago

Level 3

Thanks @bugsysha this works, but is there any way to avoid orderByRaw and would it be susceptible to SQL injection?

bugsysha

5 years ago

Level 61

You are very welcome. Thanks for the "Best Answer".

I do not know of a way on top of my head to do it without orderByRaw. If you use it as I've wrote then you are safe cause you are passing $title as a binding (second) argument.

If you wrote it like orderByRaw('(title = '.$title.') DESC, LENGTH(title)') then you would be susceptible to SQL injection.

So to be clear, with the way I've wrote it orderByRaw('(title = ?) DESC, LENGTH(title)', $title) you are as safe as you can be with Laravel and PHP.

1 like

CLab

5 years ago

Level 3

Great thanks.

Please or to participate in this conversation.