Filament•2y ago

How to add a filter from a sub relation?

I need to add a filter to the table builder that filters the current incident by district. A incident has a mm locations() relationship and a location belongs to a district (m to 1). How can I get the the districts from the locations and filter the incidents? This doesn't work unfortunately:

            Tables\Filters\SelectFilter::make('district')
                ->relationship('locations.district', 'name')

            Tables\Filters\SelectFilter::make('district')
                ->relationship('locations.district', 'name')

Thank you!

7 Replies

ProdexOP•2y ago

If I use a hasManyThrough on my incident model like so:

    public function districts()
    {
        return $this->hasManyThrough(District::class, Location::class, 'location_id', 'id');
    }

    public function districts()
    {
        return $this->hasManyThrough(District::class, Location::class, 'location_id', 'id');
    }

All Districts get displayed, but if I want to filter by one the follwing exception is thrown: Column not found: 1054 Unknown column 'locations.location_id' in 'where clause'

awcodes•2y ago

You’re relationship is ‘districts’ not ‘district’

ProdexOP•2y ago

yeah, but either way, it doesn't work with the same exception.

awcodes•2y ago

Just ideas from the top of my head. I don't use filters much. Hopefully someone else can give you a better answer.

ProdexOP•2y ago

can anyone else help me with this? My current code is as follows:

            Tables\Filters\SelectFilter::make('districts')
                ->relationship('districts', 'name'),

            Tables\Filters\SelectFilter::make('districts')
                ->relationship('districts', 'name'),

    public function districts()
    {
        return $this->hasManyThrough(District::class, Location::class, 'location_id', 'id');
    }

    public function districts()
    {
        return $this->hasManyThrough(District::class, Location::class, 'location_id', 'id');
    }

Page loads fine, but if I want to filter by district this exception appears:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'locations.location_id' in 'where clause'

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'locations.location_id' in 'where clause'

Thank you! 🙏

Tally•2y ago

Which table are you building? The Location one? or the incicents? seems the locations table does not have a location_id

ProdexOP•2y ago

I solved it now with a custom filter:

Tables\Filters\Filter::make('district')
                ->form([
                    Select::make('selected_district')
                        ->label(trans('districts.singular'))
                        ->options(
                            fn () => District::all()->pluck('name', 'id')
                        ),

                ])
                ->query(function (Builder $query, array $data): Builder {
                    return $query
                        ->when(
                            $data['selected_district'],
                            fn (Builder $query, $district) => $query->whereHas('locations', fn (Builder $query) => $query->where('district_id', $district))
                        );
                })->indicateUsing(function (array $data): ?string {
                    return $data['selected_district'] ? trans('districts.singular') . ': ' . District::find($data['selected_district'])->name : null;
                })->label(trans('districts.singular')),

Tables\Filters\Filter::make('district')
                ->form([
                    Select::make('selected_district')
                        ->label(trans('districts.singular'))
                        ->options(
                            fn () => District::all()->pluck('name', 'id')
                        ),

                ])
                ->query(function (Builder $query, array $data): Builder {
                    return $query
                        ->when(
                            $data['selected_district'],
                            fn (Builder $query, $district) => $query->whereHas('locations', fn (Builder $query) => $query->where('district_id', $district))
                        );
                })->indicateUsing(function (array $data): ?string {
                    return $data['selected_district'] ? trans('districts.singular') . ': ' . District::find($data['selected_district'])->name : null;
                })->label(trans('districts.singular')),

Gaming

Programming

How to add a filter from a sub relation?