F
Filament•2y ago
Sesh

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
toeknee•2y 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()
Sesh
SeshOP•2y 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
Sesh
SeshOP•2y 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
morty•11mo ago
@Sascha did you ever figure this out? I have the same issue.
toeknee
toeknee•11mo ago
So relationship does overwrite the options, if you place the options after the relationship does that work?
morty
morty•11mo 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);
                        });
                });
            });
    }),
Sesh
SeshOP•10mo 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); } ) ); })

Did you find this page helpful?