Laravel search filtering cannot single filter

Question

I have multiple dropdown search filter. I can filter all the column of my table if only I select every dropdown. If I'm only selecting one of them or two, it doesn't return any result. Is there anything to add or did I do it wrongly?

Eloquent

$query = Table::when($id, function($q) use ($id){
                return $q->where('id', $id);
            })
            ->when($state, function($q) use ($state){
                return $q->where('state', $state);
            })
            ->when($country, function($q) use ($country){
                return $q->where('country', $country);
            })
            ->when($name, function($q) use ($name){
                return $q->where('name', $name);
            })
            ->when($city, function($q) use ($city){
                return $q->where('city', $city);
            })
            ->when($status, function($q) use ($status){
                return $q->where('status', $status);
            })
        ->paginate(10);

return $query;

Controller

if ($request->id || $request->state || $request->country|| $request->name || $request->city || $request->name || $request->status){
      $filter = $eloquent->getTable($request->id, $request->state, //all request);
}
else{
     $filter = $eloquent->all();
}

Or is there any other way that can be done? Guide and help will be appreciated.

Couldnt notice anything wrong with the query itself. Sometimes when stuck like this analysing the resulting sql query to see if it actually produces what I want helps me. Sometimes I tend to run that sql in mysql/db admin to see if it actually fetches data. You can use toSql() instead of paginate to see the query. — user3532758
– user3532758, Commented Aug 28, 2021 at 1:32
I think when you're saying "If I'm only selecting one of them or two, it doesn't return any result", it means that the one of those 2 fields is "id" (I just assume that). So in that case probably you've filtered "id" and another field as well, which returns nothing. Try to select anything 2 columns except from "id", then you could get something. So I think the problem is that you're filtering "id". Is that make sense ?? — boolfalse
– boolfalse, Commented Aug 28, 2021 at 1:33
@boolfalse actually the id field is a search field while others are dropdown — NewbieCoder
– NewbieCoder, Commented Aug 28, 2021 at 1:38
Anyways, try to build your query part by part. Try only with 1 field, then 2, then 3, etc.. With that you can find the issue. (Don't forget sometimes clear the cache) — boolfalse
– boolfalse, Commented Aug 28, 2021 at 1:41
@boolfalse just did with the first two when clause. Can filter the second when clause alone and both clause. The first when return nothing. — NewbieCoder
– NewbieCoder, Commented Aug 28, 2021 at 1:55

yecademy · Accepted Answer · 2021-08-28 06:42:00Z

1

Try this:

$query = Model::query();

if(isset($id))
 $query->where('id', $id);
if(isset($state))
 $query->where('state', $state);
if(isset($country))
 $query->where('country', $country);
if(isset($name))
 $query->where('name', $name);
if(isset($city))
 $query->where('city', $city);
if(isset($status))
 $query->where('status', $status);
return $query->paginate(10);

answered Aug 28, 2021 at 6:42

yecademy

377 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NewbieCoder Over a year ago

Just try it, it still return the same result. I just update my controller, might be the reason why it doesn't work.

yecademy Over a year ago

@NewbieCoder send complete controller. I think that your problem is in your controller

archvayu · Accepted Answer · 2021-08-28 02:59:09Z

0

declare your query first then you can climb up the condition tree from the lowest value from your dropdown selections;

$query = Table::when($status, function($q) use ($status){
            return $q->where('status', $status);
        });

if(isset($city)) {
    $query = $query->when($city, function($q) use ($city) {
                 return $q->where('city', $city);
             });
}

if(isset($name)) {
    $query = $query->when($name, function($q) use ($name) {
                 return $q->where('name', $name);
             });
}
// climb up the condition tree you want to check from least dependent variable to most dependent variable
...
...
// return filtered results
return $query->paginate(10);

edited Aug 28, 2021 at 2:59

answered Aug 28, 2021 at 2:46

archvayu

4484 silver badges15 bronze badges

5 Comments

NewbieCoder Over a year ago

I got an error ''syntax error, unexpected ';', expecting ')''' I think there should be a bracket before }; I did put the bracket and the error fix but the result still the same.

archvayu Over a year ago

what fields are mandatory on your form? you can omit few conditions or even make a new one depending on your needs

NewbieCoder Over a year ago

non is mandatory, user can select any filter field. Either all or only one or two

archvayu Over a year ago

check the inputs value from the from if they are showing up fine; try with one selection first and move on to the next; there has to ba a way; keep trying

NewbieCoder Over a year ago

Try a different approach, seems like it still return the same result. I just edit and add up my controller. Might be the reason why it doesn't work..

Collectives™ on Stack Overflow

Laravel search filtering cannot single filter

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related