Search Filter Value A or Value B

Trying to make a simple search end point that takes one parameter provided by the end user. That value can be either "name" or "location".

I setup my code as such:

        $result = Employee::when($request->department, function ($query) use ($request) {
            $query->where('department', '=', $request->department);
        })
            ->when($request->name, function ($query) use ($request) {
                $query->where('name', '=', $request->name);
            })
           ->when($request->location, function ($query) use ($request) {
                $query->where('location', '=', $request->location);
            })
            ->when($request->employeeNumber, function ($query) use ($request) {
                $query->where('employee_number', '=', $request->employeeNumber);
            })
            ->when($request->status, function ($query) use ($request) {
                $query->where('status', '=', $request->status);
            })
            ->when($request->sortBy, function ($query) use ($request) {
                $finalSort = $this->fixFilterCase($request->sortBy);
                $sortOrder = $request->sortOrder ? $request->sortOrder : "desc";
                $query->orderBy($finalSort, $sortOrder);
            })
            ->get();

        return response()->json($result, 200);

Currently I'm trying to apply both filters in the URL ?name=<name>&location=<location>

but currently it just returns no results because its a doing an WHERE AND and not a WHERE OR Any suggestions?

Probably an easy one but I am kind of sleep deprived ha.

Nakov

1 year ago

Best Answer

Level 73

The third parameter of when is the false part which means if the first condition is not true, then apply the latter. So I would replace this:

->when($request->name, function ($query) use ($request) {
    $query->where('name', '=', $request->name);
})
->when($request->location, function ($query) use ($request) {
    $query->where('location', '=', $request->location);
})

with this:

->when($request->name && $request->location, function($query) use ($request) {
	$query->where(function($q) use ($request) {
        $q->where('name', $request->name)->orWhere('location', $q->location);		
    });
}, function ($query) use ($request) {
	$query->when($request->name, function ($query) use ($request) {
        $query->where('name', '=', $request->name);
    })
    ->when($request->location, function ($query) use ($request) {
        $query->where('location', '=', $request->location);
    });
})

1 like

SoapyIllusion

1 year ago

Level 3

That makes perfect sense. My brain was going in a VERY different direction. Thank You!

Please or to participate in this conversation.