first match exact then related data in like query mysql laravel-5

Question

I have checked this, this links for the solution but cannot find the suitable one for me.

I am trying to retrieve the records which have exact matches first and then related matches.

Here is what I have tried so far.

$facility = DB::table('facility')
    ->select('ID', 'Name', 'Address', 'Phone', 'Latitude', 'Longitude')
    ->where('Name', 'like', $q . '%')
    ->where('Name', '!=', '')
    ->limit(10)
    ->get();
if(empty($facility)) {
    $facility1 = DB::table('facility')
    ->select('ID', 'Name', 'Address', 'Phone', 'Latitude', 'Longitude')
    ->where('Name', 'like', '%' . $q . '%')
    ->where('Name', '!=', '')
    ->limit(10)
    ->get();
}
$facility->{'facility1'} = $facility1;

I have tried to merge this 2 objects. But I'm not getting the desired output.

Is there any way to get output with just one query?

EDIT: i.e. I just want to retrieve the records with exact match first and then other records. Like If I search for "OOS" then the First record should be "OOS Healthcare" and then "Fat loss" likewise.

what you want is not clear on this `if(empty($facility)) {` ? — Niklesh Raut, Apr 25 '18 at 06:58
@C2486 I just want to retrieve the records with exact match first and then other records. Like If I search for "OOS" then First record should be "OOS Health care" and then "Fat loss" etc. — Keyur, Apr 25 '18 at 07:01

Ultimater · Accepted Answer · 2018-04-25T07:52:26.057

I'm thinking make a query where you check for both likes, then select a pseudo column as the result of whether the first "like" matches, if false you can assume the second matched. Then simply order by the pseudo column.

In raw MySQL, I think this would be:

SELECT ID, Name, Address, Phone, Latitude, Longitude,
IF(name = '$q',2,IF(name LIKE '$q%',1,0)) as `MatchStrength`
FROM facility
WHERE name like '%$q%' AND name != ''
ORDER BY MatchStrength DESC LIMIT 10

You can also do this without the extra column:

SELECT ID, Name, Address, Phone, Latitude, Longitude
FROM facility
WHERE name like '%$q%' AND name != ''
ORDER BY IF(name = '$q',2,IF(name LIKE '$q%',1,0)) DESC LIMIT 10

Converting this over to ORM logic is another challenge. Can try running as raw SQL with DB::select

It's ugly, and probably vulnerable to SQL injection, but give this a shot to see if it gets you anywhere:

$facility = DB::table('facility')
    ->select('ID', 'Name', 'Address', 'Phone', 'Latitude', 'Longitude')
    ->where('Name', 'like', "%{$q}%")
    ->where('Name', '!=', '')
    ->orderByRaw("IF(name = '{$q}',2,IF(name LIKE '{$q}%',1,0)) DESC")
    ->limit(10)
    ->get();

Probably can swap over to prepared queries by doing the concatenating within MySQL using CONCAT

@KeyurK I updated the code. Think the key here is a `MatchStrength` column you can sort on. — Ultimater, Apr 25 '18 at 07:31

score 1 · Answer 2 · answered Jan 30 '19 at 10:18

The second question you mention nails it IMHO.

I was able to use it in Laravel with the orderByRaw Eloquent expression.

$facility1 = DB::table('facility')
->select('ID', 'Name', 'Address', 'Phone', 'Latitude', 'Longitude')
->where('Name', 'like', '%' . $q . '%')
->where('Name', '!=', '')
->orderByRaw("(Name = '{$q}') desc, length(Name)")
->limit(10)
->get();

This will get all terms like the one you are after, with the exact match at the top. If none are exact matches, it will just return the top 10.

Here is an excerpt from my app:

$term = Term::where('title', 'LIKE', "%" . strtolower($query) . "%")
           ->orderByRaw("(title = '{$query}') desc, length(title)")
           ->first();

first match exact then related data in like query mysql laravel-5

2 Answers2