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!!

Select filter with distinct values of an related table

Select filter with distinct values of an related table

Similar Threads

Similar Threads

Similar Threads