Postgres and Laravel search query

Question

I have a query for multiple searches, the problem is with one of them, I get a correct result from it, but in combination with the rest, I get the wrong response for the rest of the fields.

//I get the correct result for first if, but the second one returns a wrong response.
        if ($request->has('search_fullname') && !is_null($request->search_fullname)) {
            $query->where('full_name', 'ILIKE', $request->search_fullname . '%')
                ->orWhere('full_name', 'ILIKE', '% ' . $request->search_fullname . '%');
        }

        if ($request->has('search_gender') && !is_null($request->search_gender)) {
            $query->where('gender', '=', $request->search_gender);
        } //like this if I have many more, the problem is strict from the first one

TsaiKoga · Accepted Answer · 2020-03-27 13:56:58Z

First of all, when you apply where(A)->orWhere(B) to query and apply new where(C)... method, it will become:

where (A and C and D ...) or B

you need to change it to

where (A or B) and C and D ....

so the first query need to be like this:

if ($request->has('search_fullname') && !is_null($request->search_fullname)) {
    $query->where(function($q) {
          $q->where('full_name', 'ILIKE', $request->search_fullname . '%')
            ->orWhere('full_name', 'ILIKE', '% ' . $request->search_fullname . '%');
    })       
}

Secondly, you can use when as condition method, it will apply to a query only when the request field is not empty.

$query->when($request->input('search_fullname'), function($q) use ($request) {
    $q->where(function($q) {
        $q->where('full_name', 'ILIKE', $request->search_fullname.'%')
          ->orWhere('full_name', 'ILIKE', '% ' . $request->search_fullname . '%');
    });   
})->when($request->input('search_gender'), function($q) use ($request) {
    $q->where('gender', '=', $request->search_gender);
});

Collectives™ on Stack Overflow

Postgres and Laravel search query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related