Aug 16, 2020

Level 1

Adding multiple dynamic conditions to a query

Hi all,

I am trying to filter the output of a certain model for my blade view. Meaning, in my Controller I do the following:

$query = Company::query();

if ($request->locations != null){
   $id = $request->locations;
   $query = $query->whereHas('locations', function($q) use ($id) {
   $q->where('location_id', '=', $id);
   });
}
elseif ($request->locationMandatory != null) {
   $query = $query->has('locations');
}

if ($request->scopes != null){
  $id = $request->scopes;
  $query = $query->whereHas('scopes', function($q) use ($id) {
     $q->where('scope_id', '=', $id);
  });
}
elseif ($request->scopeMandatory != null) {
  $query = $query->has('scopes');
}

$companies = $query->get();

In my frontend I just do @foreach to list all the "$companies" I try to filter.

Basically, that works, though the Controller is not able to process multiple filter elements in 1 variable. E.g. if I want to filter for multiple "locations" the Controller only takes the last location-id that he is given.

The filter logic in the frontend looks as follows:

<form action="{{ route('...') }}" method="post">
@csrf
...
<select name="location[]" multiple="multiple" class="form-control filterselect @error('location') is-invalid @enderror" id="location" style="width: 100%">
   @foreach ($locations as $location)
     <option value="{{ $location->id }}">{{ $location->name }}<option>
   @endforeach
</select>
...
Submit
</form>

Anyone can help me with this?

Thanks acrm

Sinnbeck

5 years ago

Level 102

Isn't location singular? No s. And an array

if (count($request->location)) {
   $ids = $request->location;
   $query = $query->whereHas('locations', function($q) use ($ids) {
   $q->whereIn('location_id', $ids);
   });
}

1 like

Snapey

5 years ago

Level 122

your form element is named $location not $locations and is an array

Does your locations related model really have a location_id column? or, actually, does Company have the location_id column?

1 like

acrm

5 years ago

Level 1

Thanks for taking the time, @Sinnbeck & @Snapey!

I added the snippet as suggested by Sinnbeck and it looks like this in my controller:

if (count($request->locations)){
 $ids = $request->location;
 $query = $query->whereHas('locations', function($q) use ($ids) {
    $q->whereIn('location_id', $ids);
 });
}

But now it throws the following error at me:

Argument 1 passed to Illuminate\Database\Query\Builder::cleanBindings() must be of the type array, null given, called in C:\xampp\htdocs\...

location_id is part of the the pivot table between the two models Company and Location.

The multiselect i am using is collecton one or multiple ids from differen instances of "Company".

Best regards acrm

Snapey

5 years ago

Level 122

you used location and locations again !

if (count($request->location)){
 $query = $query->whereHas('locations', function($q) use ($request) {
    $q->whereIn('id', $request->location);
 });
}

make sure in the form, the field is still named location[] if you rename it to locations[] then change it in two places above

don't reference the pivot table in your query

This is assuming that your Company model has a relationship belongsToMany(Location::class)

1 like

acrm

5 years ago

Level 1

Hi @snapey, thanks again. Actually, I messed my copy/paste up. I have the multiselect in some other blade, too - and copied that one.

Now, I simply copied what you posted above, so I have the following. In the blade:

<select class="filterselect" name="location[]" multiple="multiple" style="width: 100%">
     @foreach ($locations as $location)
           <option value="{{ $location->id }}">{{ $location->name }}</option>
     @endforeach
 </select>

In the controller:

 if (count($request->location)){
    $query = $query->whereHas('locations', function($q) use ($request) {
      $q->whereIn('id', $request->location);
   });
}

The following error is displayed:

 SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select * from `companies` where exists (select * from `locations` inner join `company_location` on `locations`.`id` = `company_location`.`location_id` where `companies`.`id` = `company_location`.`company_id` and `id` in (1, 3)))

Snapey

5 years ago

Best Answer

Level 122

add the table name

 if (count($request->location)){
    $query = $query->whereHas('locations', function($q) use ($request) {
      $q->whereIn('locations.id', $request->location);
   });
}

1 like

acrm

5 years ago

Level 1

Thanks, it's working. Will review this in order to learn!

Thanks acrm

Please or to participate in this conversation.