Filament•16mo ago

Select filter grouping options in relation filter

I am struggling to get a Select filter on a relation working with option groups described here: https://filamentphp.com/docs/3.x/forms/fields/select#grouping-options This is my Select field in the table filters:

SelectFilter::make('trends')
     ->label(__('Trends'))
     ->searchable()
     ->options($formattedData)
     ->preload(),

SelectFilter::make('trends')
     ->label(__('Trends'))
     ->searchable()
     ->options($formattedData)
     ->preload(),

The format of the options is right and they get displayed correctly with the groups, butwhen i select an entry, I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'trends' in 'where clause'
SELECT
  count(*) AS aggregate
FROM
  `news`
WHERE
  (`trends` = 1)

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'trends' in 'where clause'
SELECT
  count(*) AS aggregate
FROM
  `news`
WHERE
  (`trends` = 1)

How can I configure the select so its checking the relation. When I use ->relation() it takes the options from the relation and not from the customized option array. Thanks.

Select - Form Builder - Filament

7 Replies

toeknee•16mo ago

electFilter::make('trends') ->label(__('Trends')) ->searchable() ->options($formattedData) ->preload(), where is $formattedData? options for should a closure: ->options(fn() => $formattedData) If it is a relationshjip, then why are you not using ->relationship()

SeshOP•16mo ago

The data is in this form (as described in the doc at https://filamentphp.com/docs/3.x/forms/fields/select#grouping-options):

Select::make('status')
    ->searchable()
    ->options([
        'In Process' => [
            'draft' => 'Draft',
            'reviewing' => 'Reviewing',
        ],
        'Reviewed' => [
            'published' => 'Published',
            'rejected' => 'Rejected',
        ],
    ])

Select::make('status')
    ->searchable()
    ->options([
        'In Process' => [
            'draft' => 'Draft',
            'reviewing' => 'Reviewing',
        ],
        'Reviewed' => [
            'published' => 'Published',
            'rejected' => 'Rejected',
        ],
    ])

If I use ->relationship() it takes the options from the relation and not from the grouped options.

Select - Form Builder - Filament

SeshOP•16mo ago

Was anyone able to get Option Groups to work with a relationship in a SelectFilter? It seems that using ->relationship() overwrites the provided ->options() so it is not displayed in groups.

morty•9mo ago

@Sascha did you ever figure this out? I have the same issue.

toeknee•9mo ago

So relationship does overwrite the options, if you place the options after the relationship does that work?

morty•9mo ago

It didn't for me. I ended up using a custom filter instead:

Tables\Filters\Filter::make('collection')
    ->label('Collection')
    ->indicateUsing(function (array $data): ?string {
        if ($data['collection']) {
            $collection = AccountCollection::where(function (Builder $query): Builder {
                return $query->ownedByCurrentUser()->orWhere('public', true);
            })->find($data['collection']);

            return "Collection: {$collection?->name}";
        }

        return null;
    })
    ->form([
        Forms\Components\Select::make('collection')
            ->label('Collection')
            ->placeholder('All')
            ->searchable()
            ->options([
                'My private collections' => AccountCollection::private()->ownedByCurrentUser()->orderBy('name')->pluck('name', 'id')->all(),
                'My public collections' => AccountCollection::public()->ownedByCurrentUser()->orderBy('name')->pluck('name', 'id')->all(),
                'Other public collections' => AccountCollection::public()->notOwnedByCurrentUser()->orderBy('name')->pluck('name', 'id')->all(),
            ]),
    ])
    ->query(function (array $data, Builder $query) {
        $query->when(
            $data['collection'],
            function (Builder $query) use ($data): Builder {
                return $query->whereHas('collections', function (Builder $query) use ($data): Builder {
                    return $query->where(with(new AccountCollection)->getTable().'.id', '=', $data['collection'])
                        ->where(function (Builder $query): Builder {
                            return $query->ownedByCurrentUser()->orWhere('public', true);
                        });
                });
            });
    }),

Tables\Filters\Filter::make('collection')
    ->label('Collection')
    ->indicateUsing(function (array $data): ?string {
        if ($data['collection']) {
            $collection = AccountCollection::where(function (Builder $query): Builder {
                return $query->ownedByCurrentUser()->orWhere('public', true);
            })->find($data['collection']);

            return "Collection: {$collection?->name}";
        }

        return null;
    })
    ->form([
        Forms\Components\Select::make('collection')
            ->label('Collection')
            ->placeholder('All')
            ->searchable()
            ->options([
                'My private collections' => AccountCollection::private()->ownedByCurrentUser()->orderBy('name')->pluck('name', 'id')->all(),
                'My public collections' => AccountCollection::public()->ownedByCurrentUser()->orderBy('name')->pluck('name', 'id')->all(),
                'Other public collections' => AccountCollection::public()->notOwnedByCurrentUser()->orderBy('name')->pluck('name', 'id')->all(),
            ]),
    ])
    ->query(function (array $data, Builder $query) {
        $query->when(
            $data['collection'],
            function (Builder $query) use ($data): Builder {
                return $query->whereHas('collections', function (Builder $query) use ($data): Builder {
                    return $query->where(with(new AccountCollection)->getTable().'.id', '=', $data['collection'])
                        ->where(function (Builder $query): Builder {
                            return $query->ownedByCurrentUser()->orWhere('public', true);
                        });
                });
            });
    }),

SeshOP•9mo ago

This is my current code that works for me:

                Filter::make('productCategories')
                    ->form([
                        Select::make('productCategories')
                            ->label(__('Product Categories'))
                            ->multiple()
                            ->preload()
                            ->optionsLimit(150)
                            ->options(
                                $groupedProductCategories
                            ),
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        return $query
                            ->when(
                                $data['productCategories'],
                                fn (Builder $query, $productCategories): Builder => $query->whereHas(
                                    'productCategories',
                                    function ($q) use ($productCategories) {
                                        $q->whereIn('product_categories.id', $productCategories);
                                    }
                                )
                            );
                    })

Gaming

Programming

Select filter grouping options in relation filter