SelectFilter updating join filter possible?

✅ SolvedTable builder4️⃣v4

Hi,

I would like to filter on a yearly basis with the SelectFilter. The SelectFilter is working but when I select a year, it does add a where statement causing the null values to be removed from the result (unwanted behavior). When I test the query with ->leftJoin->where (added as example) it correctly filters the value and also the null values.

The question: Is there a way to update the query with the correct join based on the SelectFilter?

Any help would be appreciated

I have the following table in filament v4:
```php
return $table
->query(
Agency::query()
->leftJoin('clothing', function (JoinClause $join) {
$join->on('agencies.id', '=', 'clothing.agency_id')
->where('clothing.year', '=', '2023'); // the year need to be updated by the SelectFilter
})
->select("agencies.id", "agencies.Code", "agencies.Aanvrager")
->selectRaw("SUM(CASE WHEN month(DatumMaakster) = 1 THEN 1 ELSE 0 END) AS 'Jan'")
// ...
->selectRaw("SUM(CASE WHEN month(DatumMaakster) = 12 THEN 1 ELSE 0 END) AS 'Dec'")
->selectRaw("Count(DatumMaakster) AS 'Total'")
->groupBy('agencies.id')

)
->columns([
TextColumn::make('Code')
TextColumn::make('Aanvrager'),
TextColumn::make('Jan')
// ...
TextColumn::make('Dec'),

TextColumn::make('Total')->sortable(),
])
->filters([
SelectFilter::make('clothing.year')
->label('Year')
->options(fn(): array => Clothing::query()->select('year')->distinct()->pluck('year', 'year')->sort()->all())
->format('Y')),
]);
}

Solution

Nah dawg, that's not it.

You should be able to do all this with a filter query and a relationship aggregate. No need to do manual joins, no need to modify the table query.

Something like this for the filter, where you define the query you want to apply (needs fleshing out to test if $data is set, etc) ...

SelectFilter::make('year')
   ->options(Clothing::query()->select('year')->distinct()->pluck('year', 'year')->sort()->all()))
   ->query(fn (Builder $query, $data) => $query
      ->whereHas('clothing', fn (Builder $query) => $query->where('year', $data['year'])
   )

SelectFilter::make('year')
   ->options(Clothing::query()->select('year')->distinct()->pluck('year', 'year')->sort()->all()))
   ->query(fn (Builder $query, $data) => $query
      ->whereHas('clothing', fn (Builder $query) => $query->where('year', $data['year'])
   )

... then an aggregate for the Total ...

TextColumn('clothing_count')
   ->counts('clothing')

TextColumn('clothing_count')
   ->counts('clothing')

I can't remember off the top of my head if the ->counts() aggregate in Filament will inherit the whereHas() from the filter, it probably won't. So you may need to finagle that in a function that return the relationship name and query modification, while pulling the filter value from the $livewire object.