CODEWITHSUNDEEP
phplaravelautocomplete
user1506104
user1506104

Reputation: 7086

Build an auto completion service with one or multiple search terms

I am building an auto completion feature for my mobile app. The results need to come from my web service built on Laravel 5.8.

api.php:

Route::get('locations/autocomplete', 'LocationsController@autocomplete');

LocationsController:

public function autocomplete(Request $request)
{
    $locations = Location::query();
    foreach($request->words as $word) {
        $locations->whereRaw('country_name LIKE ? OR state_name LIKE ? OR city_name LIKE ? ', ['%'.$word.'%','%'.$word.'%','%'.$word.'%']);
    } 
    $locations = $locations->distinct()->paginate(10);

    return AutoCompleteLocationResource::collection($locations);
}

When I do a GET request to localhost:8000/api/locations/autocomplete?words[]=united&words[]=atlanta, it gives me a result as if I wrote it using $locations->orWhereRaw:

select * from locations where 
country_name LIKE %united% OR state_name LIKE %united% OR city_name LIKE %united% 
AND 
country_name LIKE %atlanta% OR state_name LIKE %atlanta% OR city_name LIKE %atlanta%

What I want is to logically separate the two blocks with an AND like so:

select * from locations where 
(country_name LIKE %united% OR state_name LIKE %united% OR city_name LIKE %united%)
AND 
(country_name LIKE %atlanta% OR state_name LIKE %atlanta% OR city_name LIKE %atlanta%)

Upvotes: 0

Views: 105

Answers (2)

rfpdl
rfpdl

Reputation: 964

Try this:

$query = Location::query();

foreach($request->words as $word) {
   $query->where(function($qry) use ($word)  {
       $qry->where('country_name', 'like', '%'.$word.'%');
       $qry->orWhere('state_name', 'like', '%'.$word.'%');
       $qry->orWhere('city_name', 'like', '%'.$word.'%');
   });
}

$locations = $query->distinct()->paginate(10);

Upvotes: 1

user1506104
user1506104

Reputation: 7086

Apparently, multiple calls to whereRaw does not logically separate each query. You have to do it manually.

This is what solved the problem.

$locations->whereRaw('(country_name LIKE ? OR state_name LIKE ? OR city_name LIKE ? )', ['%'.$word.'%','%'.$word.'%','%'.$word.'%']);

Notice the extra '(' and ')' characters at the start and end of the first parameter for whereRaw.

Upvotes: 0

Related Questions