Filament•8mo ago

Correct way to apply a `withCount()` and `->having()` in a SelectFilter?

I have a Domain that has many Sites and I want to filter by the total number of sites each domain has. ie a drop down with eg 1, 2, 3 etc. I feel I am sort of close, but can't seem to get the last part working;

                Tables\Filters\SelectFilter::make('sites_count')
                    ->label(__('# Sites'))
                    ->modifyQueryUsing(fn ($query, Tables\Filters\BaseFilter $filter) => $query->withCount('sites')->having('sites_count', $filter->getState()))
                    ->options(Site::getDomainCountAsArray())
                    ->multiple(),

                Tables\Filters\SelectFilter::make('sites_count')
                    ->label(__('# Sites'))
                    ->modifyQueryUsing(fn ($query, Tables\Filters\BaseFilter $filter) => $query->withCount('sites')->having('sites_count', $filter->getState()))
                    ->options(Site::getDomainCountAsArray())
                    ->multiple(),

This seems to produce the following SQL;

SELECT `domains`.*, (SELECT count(*) FROM `sites` WHERE `domains`.`id` = `sites`.`domain_id`) AS `sites_count` FROM `domains` ORDER BY `created_at` DESC LIMIT 50 OFFSET 0

SELECT `domains`.*, (SELECT count(*) FROM `sites` WHERE `domains`.`id` = `sites`.`domain_id`) AS `sites_count` FROM `domains` ORDER BY `created_at` DESC LIMIT 50 OFFSET 0

So it seems to not take ->having() into account at all? Basically I'm able to get ->withCount('sites') working, but unable to use it in the query.

1 Reply

CTOP•8mo ago

bump? Solved. For anyone else wondering in the future this was the only way I could get it working. I was unable to get ->having() working inside of ->modifyQueryUsing() alone.

                Tables\Filters\SelectFilter::make('sites_count')
                    ->label(__('# Sites'))
                    ->baseQuery(fn(Builder $query, $state) => ctype_digit($state['value']) ? $query->having('sites_count', '=', $state['value']) : $query)
                    ->modifyQueryUsing(fn(Builder $query) => $query)
                    ->options(Domain::getSitesCountAsArray()),

                Tables\Filters\SelectFilter::make('sites_count')
                    ->label(__('# Sites'))
                    ->baseQuery(fn(Builder $query, $state) => ctype_digit($state['value']) ? $query->having('sites_count', '=', $state['value']) : $query)
                    ->modifyQueryUsing(fn(Builder $query) => $query)
                    ->options(Domain::getSitesCountAsArray()),

->options() returns an array of site counts, eg [0,1,2,3] ctype_digit() was necessary because most casting doesn't play nicely with the string "0" modifyQueryUsing() is necessary to remove the where condition that filament applies by default, ie WHERE sites_count = 0

Gaming

Programming

Correct way to apply a `withCount()` and `->having()` in a SelectFilter?