I am having problems with my search function working properly. Currently, I have a search form with two radio buttons('keywords' and 'exact') and depending on what they have selected, it should search the database for the given array of strings or single string(both the title AND description together SHOULD contain all of the searched keywords). The issue arises when the user searches via keywords (a bunch of single word strings). Presently when i fetch the string from the form, I explode it and then run a foreach loop through it, however, it ends up messing up due to SQL's ordering of 'OR' and 'AND' operators.
For instance, a search value of search_value=black+darker would $query->toSql() to
"select * from `animes` where `description` like ? or `name` like ? and `description` like ? or `name` like ?"
However, SQL reads it as
"select * from `animes` where (`description` like ?) or (`name` like ? and `description` like ?) or (`name` like ?)"
Here is my code(ignore anything related to 'genre' as that part works correctly as expected):
public function searchAnimes(SearchRequest $request)
{
if($request->search_type == 'keyword')
{
$animeSearchValues = explode(' ', Input::get('search_value'));
}
elseif($request->search_type == 'exact')
{
$animeSearchValues[0] = Input::get('search_value');
}
else
{
return redirect('animes');
}
$genres = $request->name;
$query = Anime::query();
if(isset($animeSearchValues) && strlen(Input::get('search_value')) >= 3)
{
foreach($animeSearchValues as $animeSearchValue)
{
$query->where('description', 'like', '%'.$animeSearchValue.'%')
->orWhere('name', 'like', '%'.$animeSearchValue.'%');
}
if(isset($genres))
{
foreach ($genres as $genre)
{
$query->whereHas('genres', function ($f) use ($genre)
{
$f->where('slug', $genre);
});
}
}
$animeResults = $query->orderBy('name')->paginate(10);
return view('animes.results', compact('animeResults'));
}
}
The only solution I have been able to think of is to take both the title and description and merge them into a single string and then search through it for each individual keyword, however, that method seems to be too jerry-rigged or nonstandard. Also, keep in mind, I need the entire thing in a single query builder as so I may display the results with pagination.
Thanks.