Filament•2y ago

Filter with recursive relationship

I have organisations that can be of different types. One type is school, which has a relation to a parent organisation, with type foundation. If I try to add a SelectFilter on School, filtering on parent Foundation, I get a bug in the resulting query. For easy comparing, I added another (non-recursive) filter.

select
    count(*) as aggregate
from
    `org__organisation`
where (
    exists (
        select
            *
        from
            `org__organisation` as `laravel_reserved_0`
        where
            `laravel_reserved_0`.`organisation_id` = `org__organisation`.`elder_id`
            and `org__organisation`.`organisation_id` = '6' # <<<<< THIS IS WRONG
            and `organisation_type_id` = 4 # <<<<< This comes from a global scope, which is correct
    )
    and
    exists (
        select
            *
        from
            `org__subregio`
        where
            `org__organisation`.`subregio_id` = `org__subregio`.`subregio_id`
            and `org__subregio`.`subregio_id` = '3'
    )
)
and `organisation_type_id` = 5

select
    count(*) as aggregate
from
    `org__organisation`
where (
    exists (
        select
            *
        from
            `org__organisation` as `laravel_reserved_0`
        where
            `laravel_reserved_0`.`organisation_id` = `org__organisation`.`elder_id`
            and `org__organisation`.`organisation_id` = '6' # <<<<< THIS IS WRONG
            and `organisation_type_id` = 4 # <<<<< This comes from a global scope, which is correct
    )
    and
    exists (
        select
            *
        from
            `org__subregio`
        where
            `org__organisation`.`subregio_id` = `org__subregio`.`subregio_id`
            and `org__subregio`.`subregio_id` = '3'
    )
)
and `organisation_type_id` = 5

Where is says and org__organisation.organisation_id = '6' it should say and organisation_id = '6' (or alias laravel_reserved_0) 1. Is this indeed a bug or am I doing it wrong? 2. If I understand correctly, anything more complex than a simple relation requires a custom Tables\Filters\Filter with a ->form() and a ->query(). Correct? 3. I created such a custom filter for the recursive relationship. It works beautifully, except it's not showing up in the "Active filters". How can I have it listed there?

7 Replies

Dan Harrin•2y ago

yes you are correct about 2 3) you need to define "indicators" (search the docs for filter indicators) in v3 we do support nested SelectFilter relationships

JohnOP•2y ago

Good to hear about v3! Looking forward to it. I tried this:

                    Filter::make('foundation')
                        ->form([
                            Select::make('foundation')->options(
                                Foundation
                                    ::orderBy('organisation')
                                    ->pluck('organisation', 'organisation_id')
                            )
                            ->label('xxx')
                        ])
                        ->query(function (Builder $query, array $data): Builder {
                            return $query
                                ->when(
                                    $data['foundation'],
                                    fn (Builder $query, $date): Builder => $query->where('elder_id', $data['foundation']),
                                );
                        })
                        ->indicator('yyy'),

                    Filter::make('foundation')
                        ->form([
                            Select::make('foundation')->options(
                                Foundation
                                    ::orderBy('organisation')
                                    ->pluck('organisation', 'organisation_id')
                            )
                            ->label('xxx')
                        ])
                        ->query(function (Builder $query, array $data): Builder {
                            return $query
                                ->when(
                                    $data['foundation'],
                                    fn (Builder $query, $date): Builder => $query->where('elder_id', $data['foundation']),
                                );
                        })
                        ->indicator('yyy'),

It gives me the "xxx" label above the select. But no indicator is shown for that filter whatsoever. The filter works properly.

Dan Harrin•2y ago

the indicator accepts a callback function, no?

JohnOP•2y ago

There is a ->indicator() that accepts a string, which doesn't work on custom filters. There is also a ->indicateUsing() that accepts a closure, which does work 🙂 This works like a charm. Looks like a regular SelectFilter now:

                    Filter::make('foundation')
                        ->form([
                            Select::make('foundation')->options(
                                Foundation
                                    ::orderBy('organisation')
                                    ->pluck('organisation', 'organisation_id')
                            )
                            ->label('xxx')
                        ])
                        ->query(function (Builder $query, array $data): Builder {
                            return $query
                                ->when(
                                    $data['foundation'],
                                    fn (Builder $query, $date): Builder => $query->where('elder_id', $data['foundation']),
                                );
                        })
                        ->indicateUsing(function (array $data): ?string {
                            return sprintf(
                                '%s: %s',
                                __('models.Foundation.1'),
                                Foundation::find($data['foundation'])->organisation
                            );
                        })

                    Filter::make('foundation')
                        ->form([
                            Select::make('foundation')->options(
                                Foundation
                                    ::orderBy('organisation')
                                    ->pluck('organisation', 'organisation_id')
                            )
                            ->label('xxx')
                        ])
                        ->query(function (Builder $query, array $data): Builder {
                            return $query
                                ->when(
                                    $data['foundation'],
                                    fn (Builder $query, $date): Builder => $query->where('elder_id', $data['foundation']),
                                );
                        })
                        ->indicateUsing(function (array $data): ?string {
                            return sprintf(
                                '%s: %s',
                                __('models.Foundation.1'),
                                Foundation::find($data['foundation'])->organisation
                            );
                        })

Thanks again @Dan Harrin !

Dan Harrin•2y ago

ah thats it

Maxxx22•2y ago

I am currently stuck on the same topic. I used https://filamentphp.com/tricks/dependent-select-filters for help, but I am not sure if the value of the filter option should be retrieved from the DB again? Is there a possibility to retrieve it from existing select options data?

Foundation::find($data['foundation'])->organisation

Foundation::find($data['foundation'])->organisation

I see SelectFilter is doing something like this:

        $this->indicateUsing(function (SelectFilter $filter, array $state): array {
            if ($filter->isMultiple()) {
                if (blank($state['values'] ?? null)) {
                    return [];
                }

                $labels = Arr::only($this->getOptions(), $state['values']);

        $this->indicateUsing(function (SelectFilter $filter, array $state): array {
            if ($filter->isMultiple()) {
                if (blank($state['values'] ?? null)) {
                    return [];
                }

                $labels = Arr::only($this->getOptions(), $state['values']);

But how to access the option from within indicateUsing?

toeknee•2y ago

Please open a new post.

Gaming

Programming

Filter with recursive relationship