CODEWITHSUNDEEP
laravelpostgresql
Beusebiu
Beusebiu

Reputation: 1523

Postgres and Laravel search query

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

Upvotes: 0

Views: 419

Answers (1)

TsaiKoga
TsaiKoga

Reputation: 13394

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);
});

Upvotes: 2

Related Questions