Eloquent grouped orWhere

Question

Currently have an Eloquent statement:

$contacts = Contacts::where('lname','LIKE',$searchquery.'%')
    ->orWhere('fname','LIKE',$searchquery.'%')
    ->orWhere('phone','LIKE','%'.$searchquery)
    ->where('active','=',1)->get();

It is treating it as

select 
    * 
from 
    contacts 
where 
    lname like $searchquery+'%' 
    or lname like $searchquery+'%' 
    or lname like $searchquery+'%' 
    and active = 1

what I am needing is

select 
    * 
from 
    contacts 
where 
    (lname like $searchquery+'%' 
    or lname like $searchquery+'%' 
    or lname like $searchquery+'%') 
    and active = 1

How do I go about grouping in Eloquent? I have found a couple examples such as:

DB::table('users')
        ->where('name', '=', 'John')
        ->orWhere(function ($query) {
            $query->where('votes', '>', 100)
                  ->where('title', '<>', 'Admin');
        })
        ->get();

But I am only used to Eloquent, not Laravels DB Query builder. I tried adapting the Eloquent form to this

$contacts = Contacts::->where('active', '=', '1')
        ->where(function ($query) {
            $query->orWhere('lname', 'LIKE', $searchquery.'%')
                  ->orWhere('lname', 'LIKE', $searchquery.'%')
                  ->orWhere('phone', 'LIKE', '%'.$searchquery);
        })
        ->get();

No success as it does not recognize the $searchquery inside the function.

What am I missing?

score 4 · Accepted Answer · answered Dec 09 '15 at 23:32

So, this is what you have to do:

DB::table('users')->where(function($query) use ($searchQuery){
                                $query->where('lname', 'LIKE', $searchQuery . '%')
                                      ->orWhere('fname', 'LIKE', $searchQuery . '%')
                                      ->orWhere('phone','LIKE', '%' . $searchquery);
                          })
                          ->get();

Note that I've put use ($searchQuery) so it can be used inside the closure

That worked! Had to add the `connection('contacts')` before the table because its not from mt default connection, but works! Thanks — Keith Clark, Dec 10 '15 at 17:43

score 1 · Answer 2 · answered Jun 22 '17 at 07:16

When you use orWhere with multiple where clauses, you need to be careful!

Where clauses after orWhere doesn't effected to sibling where clauses before orWhere clauses such as where, whereHas, whereDoesntHave, orWhereHas

also you have to pass $searchquery variable inside to function in where clause using use ($searchquery)

Contacts::->where(function ($query) use ($searchquery) {
                $query->orWhere('lname', 'LIKE', $searchquery.'%')
                      ->orWhere('lname', 'LIKE', $searchquery.'%')
                      ->orWhere('phone', 'LIKE', '%'.$searchquery);
            })
            ->where('active', '=', '1')
            ->get();

Eloquent grouped orWhere

2 Answers2