Filament•5w ago

Sort one to many TextColumn based on formatted state?

Hey team, I am having another moment... is there a way to sort a one to many text column based on the concatenated string? Column:

TextColumn::make('citizenships.name')
    ->searchable()
    ->formatStateUsing(
        fn($record) => $record->citizenships->map(fn($citizenship) =>  $citizenship->name)->join(', ')
    )
    ->sortable(),

TextColumn::make('citizenships.name')
    ->searchable()
    ->formatStateUsing(
        fn($record) => $record->citizenships->map(fn($citizenship) =>  $citizenship->name)->join(', ')
    )
    ->sortable(),

Throws this error, as one would expect if it was using the column raw data to sort.

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

Any advice is greatly appreciated.

Solution:

Can’t you add the virtual column to the table with the data and then use that column for sorting?

Jump to solution

4 Replies

Brian Kidd•5w ago

Someone may have a way to do this in filament but I would create a model scope that adds this field at the database level or create a virtual column on the database that does this.

403gtfo•5w ago

ooooh that might work. I havent used virtual columns before but I'll give it a crack and check back. Many thanks. Bah, I cant seem to pull the relation data into a virtual column.

Solution

Brian Kidd•4w ago

Can’t you add the virtual column to the table with the data and then use that column for sorting?

Bruno Pereira•4w ago

TextColumn::make('citizenships.name')
    ->sortable(query: function (Builder $query, string $direction): Builder {
        return $query
            ->orderBy('last_name', $direction)
            ->orderBy('first_name', $direction);
change to your query

    })
    ->formatStateUsing(
        fn($record) => $record->citizenships->map(fn($citizenship) =>  $citizenship->name)->join(', ')
    )
    ->searchable(),

TextColumn::make('citizenships.name')
    ->sortable(query: function (Builder $query, string $direction): Builder {
        return $query
            ->orderBy('last_name', $direction)
            ->orderBy('first_name', $direction);
change to your query

    })
    ->formatStateUsing(
        fn($record) => $record->citizenships->map(fn($citizenship) =>  $citizenship->name)->join(', ')
    )
    ->searchable(),

maybe that does the trick

Gaming

Programming

Sort one to many TextColumn based on formatted state?