Laravel Query is not working as aspected on multiple where and orWhere?

July 23, 2022

I have a data having table name users that has fields of id, role_id, first_name, last_name, name, and email. What I trying to get is the role_id of 2 with any match from the name, first_name, or last_name.

Laravel Framework 8.77.1

PHP 7.4.3

The database table is

What I am trying is the Laravel Query

public function search_friend_custom(Request $request) {
    $search = $request->input('search');
    $search = 'chase '; 
    // ** $Search variable has chase string for searching.**

    $friends = DB::table('users')
            
            ->select('users.*','users.id as user_tab_id')

            ->where('role_id', 2)
            ->where('name', 'like', '%' . $search . '%')

            ->orWhere('first_name', 'like', '%' . $search . '%')
            ->orWhere('last_name', 'like', '%' . $search . '%')
    
            ->paginate(10); 

    return view('user.custom_search' , compact('friends'));

}

What it is returing are both Chase’s Hadyman Services with role_id=3 and Chase Mary role_id=2 both. But the aspected result is only Chase Mary as it's role_id is 2.

Please guide, I am not getting what I am missing here?

>Solution :

You should logically group them inside a closure so your role_id takes precedence but doesn’t interfere with the rest of the query.

$friends = DB::table('users')
    ->select('users.*','users.id as user_tab_id')
    ->where('role_id', 2)
    ->where(fn($builder) => $builder->where('name', 'like', '%' . $search . '%')
        ->orWhere('first_name', 'like', '%' . $search . '%')
        ->orWhere('last_name', 'like', '%' . $search . '%')
    )->paginate(10);

See the documentation for more references.