I needed to make an Eloquent query that could take search terms. The search terms are optional – no search term and the whole dataset is returned. In our particular data model the Users have one or many Associations.
So the search term should check for user name, phone, email and association name. In the sub queries you simply foreach through the search terms and check if there is a match in any column.
After the when-clause you can add any where-clauses that applies to every query, no matter the result of the search. In this case we only wanted users that are admins.
$search = $request->get('search');
$searchTerms = null;
// Split search string at spaces if there is one
if ($search) {
$searchTerms = explode(' ', $search);
}
$users = User::orderBy($'created_at', 'DESC')->
with('associations')->
when($searchTerms, function ($q) use ($searchTerms) {
foreach ($searchTerms as $searchTerm) {
$q->orWhereHas('associations', function ($qa) use ($searchTerm) {
$qa->where('name', 'LIKE', "%{$searchTerm}%");
});
$q->orWhere('first_name', 'LIKE', "%{$searchTerm}%");
$q->orWhere('last_name', 'LIKE', "%{$searchTerm}%");
$q->orWhere('phone', 'LIKE', "%{$searchTerm}%");
$q->orWhere('email', 'LIKE', "%{$searchTerm}%");
}
})->
where('is_admin', 1)->get();