Laravel Eloquent Search Multiple Words from Different Fields

Question

I currently have the following code which is working...

$searchString = $request->searchstring;
$searchValues = preg_split('/\s+/', $searchString, -1, PREG_SPLIT_NO_EMPTY);
$cards=Card::where(function ($q) use ($searchValues){
    foreach($searchValues as $value){
        $q->orWhere('firstname', 'like', "%{$value}%");
        $q->orWhere('lastname', 'like', "%{$value}%");
        $q->orWhere('cardset', 'like', "%{$value}%");
    }
})->paginate(24);

It is producing the following sql when I do a dd...

select * from "cards" where ("firstname" like ? or "lastname" like ? or "cardset" like ? or "firstname" like ? or "lastname" like ? or "cardset" like ?)

but the sql I would like should be...

select * from "cards" where ("firstname" like ? or "lastname" like ? or "cardset" like ?) AND ("firstname" like ? or "lastname" like ? or "cardset" like ?)

I've tried a couple of things but can't seem to get it.

Thanks.

Answer 1

To get the following sql query

select * from "cards" where ("firstname" like ? or "lastname" like ? or "cardset" like ?) AND ("firstname" like ? or "lastname" like ? or "cardset" like ?)

Try this..

$cards = Card::Query();

foreach($searchValues as $value)
{
    $cards = $cards->where(function ($q) use ($value) {
                    $q->orWhere('firstname', 'like', "%{$value}%");
                    $q->orWhere('lastname', 'like', "%{$value}%");
                    $q->orWhere('cardset', 'like', "%{$value}%");
                });
}

$cards = $cards->paginate(24);