Select filter with distinct values of an related table
I’m using Filament 4. I have two tables/models:
- incoming_invoices (belongsTo holidayWeek)
- holiday_weeks (hasMany incomingInvoices, contains an integer year column)
In the table, showing the related year works fine: TextColumn::make('holidayWeek.year')
Now I want a Select filter on the IncomingInvoiceResource that lists all distinct year values from holiday_weeks, and filters invoices to those whose related holidayWeek.year matches the selected value.
The SQL I have in mind:
-- options list
select year from holiday_weeks group by year order by year desc;
-- filtering effect
select incoming_invoices.*
from incoming_invoices
inner join holiday_weeks on holiday_weeks.id = incoming_invoices.holiday_week_id
where holiday_weeks.year = 2024;
What I tried:
#Option 1
SelectFilter::make('year')->relationship('holidayWeek', 'year');
Doesn’t show distinct values and errors because year isn’t a column on incoming_invoices.
#Option 2
SelectFilter::make('year')
->label('Jaar')
->default(date('Y'))
->options(fn () => HolidayWeek::query()
->distinct()
->orderByDesc('year')
->pluck('year', 'year')
->toArray()
);
This gives distinct options, but the table isn’t actually filtered (the filter key doesn’t map to a base column and I’m not sure how to apply it to the relation).
#Option 3
SelectFilter::make('holidayWeek.year')
Also doesn’t work (filter fields need to exist on the base table).
Question
What’s the proper Filament 4 way to:
- populate the filter with distinct holiday_weeks.year values, and
- apply the selection to the table query so only invoices with that related year are shown (either via whereHas or a join)?
Environment
Filament 4.x
Laravel (12)
PHP 8.2+
Thanks for help!!
- incoming_invoices (belongsTo holidayWeek)
- holiday_weeks (hasMany incomingInvoices, contains an integer year column)
In the table, showing the related year works fine: TextColumn::make('holidayWeek.year')
Now I want a Select filter on the IncomingInvoiceResource that lists all distinct year values from holiday_weeks, and filters invoices to those whose related holidayWeek.year matches the selected value.
The SQL I have in mind:
-- options list
select year from holiday_weeks group by year order by year desc;
-- filtering effect
select incoming_invoices.*
from incoming_invoices
inner join holiday_weeks on holiday_weeks.id = incoming_invoices.holiday_week_id
where holiday_weeks.year = 2024;
What I tried:
#Option 1
SelectFilter::make('year')->relationship('holidayWeek', 'year');
Doesn’t show distinct values and errors because year isn’t a column on incoming_invoices.
#Option 2
SelectFilter::make('year')
->label('Jaar')
->default(date('Y'))
->options(fn () => HolidayWeek::query()
->distinct()
->orderByDesc('year')
->pluck('year', 'year')
->toArray()
);
This gives distinct options, but the table isn’t actually filtered (the filter key doesn’t map to a base column and I’m not sure how to apply it to the relation).
#Option 3
SelectFilter::make('holidayWeek.year')
Also doesn’t work (filter fields need to exist on the base table).
Question
What’s the proper Filament 4 way to:
- populate the filter with distinct holiday_weeks.year values, and
- apply the selection to the table query so only invoices with that related year are shown (either via whereHas or a join)?
Environment
Filament 4.x
Laravel (12)
PHP 8.2+
Thanks for help!!
