CODEWITHSUNDEEP
phpmysqllaravel
Old Ceylon Mudliar
Old Ceylon Mudliar

Reputation: 188

Multiple where conditions with 'AND' & 'OR' operators in laravel

in my laravel controller i'm trying to perform the following query for the user search.

$data = User::orderBy('id','DESC')
            ->where('email','LIKE','%'.$request->search."%")
            ->orwhere('first_name','LIKE','%'.$request->search."%")
            ->orwhere('last_name','LIKE','%'.$request->search."%")
            ->WHERE('role_id','=','3')
            ->paginate(12);

            return view('admins.participants.results',compact('data'))

                ->with('i', ($request->input('page', 1) - 1) * 12 );

What I really want to perform is,

SELECT * FROM USERS WHERE role_id='3' AND email LIKE '%search_string%' OR first_name LIKE '%search_string%' OR last_name LIKE '%search_string%' ;

But the above laravel query outputs all the users without considering the `role_id=3

Upvotes: 0

Views: 159

Answers (2)

aynber
aynber

Reputation: 23011

You need to pass OR clauses to a Closure to group them, otherwise the query doesn't really understand which clauses are optional and which are not.

$data = User::orderBy('id','DESC')
        ->where('role_id','=','3')
        ->where(function($query) use ($request) {
              $query->where('email','LIKE','%'.$request->search."%")
              ->orwhere('first_name','LIKE','%'.$request->search."%")
              ->orwhere('last_name','LIKE','%'.$request->search."%");
        })
        ->paginate(12);

Upvotes: 1

John Lobo
John Lobo

Reputation: 15319

You can use where callback

  $data = User::orderBy('id','DESC')
        ->where('role_id',3)
        ->where(function($query)use($request){
            $query->where('email','LIKE','%'.$request->search."%");
            $query->orwhere('first_name','LIKE','%'.$request->search."%");
            $query->orwhere('last_name','LIKE','%'.$request->search."%");
        })->paginate(12);

also you can change

'%'.$request->search."%"

to

"%{$request->search}%"

Upvotes: 1

Related Questions