how to use Laravel 8 query builder like eloquent for searching

I’m developing a simple CRM with Laravel 8 that needs to search in query string.

My query string would be look like:

http://127.0.0.1:8000/admin/users/supervisor?q=john&gender=m

Now the problem is how can I search in controller?

I want to do like this:

public function index (Request $request)
{
    $role = getRoleCode($request->role);
    $roles = Role::where('role', '=', $role);

    if ($request->q) {
        $roles->where('name', 'like', "%$request->$q%");
    }

    if ($request->gender) {
        $roles->where('gender', '=', $request->gender);
    }

    $role->orderBy('id', 'desc')->paginate(20);

    return view('admin.users.index', [
        'roles' => $roles,
        'role_name' => config('settings.roles')[$role],
        'role_en_name' => $request->role,
        'q' => $request->q,
        'gender' => $request->gender
    ]);
}

I wonder why its not working and what is the standard way to do this.

I’ve tried:

Role::query();

but that didn’t work either.

I’ve also tried:

$roles = Role::where('role', '=', $role)
    ->where('name', 'like', "%$request->$q%")
    ->where('gender', '=', $request->gender)
    ->orderBy('id', 'desc')
    ->paginate(20);

This codes works perfectly but we may not be sending the “q” or “gender” URL params.

PS: Sorry for my bad English 🙂

Answer

If you want to conditionally add where statements to your query you can use the if statements like you are attempting to do or use the when method of the Eloquent/Query Builder:

$roles = Role::where('role', $role)
    ->when($request->input('q'), fn ($query, $search) => $query->where('name', 'like', '%'. $search .'%'))
    ->when($request->input('gender'), fn ($query, $gender) => $query->where('gender', $gender))
    ->orderBy('id', 'DESC')
    ->paginate(20);

Laravel 8.x Docs – Queries – Conditional Clauses when