Exporting a sorted sum column with ExportAction

Im trying to export a list of users with a sum column. im my table for users i added this:

   TextColumn::make('rides_sum_distance')
                    ->label('Total distance')
                    ->sum('rides', 'distance')
                    ->default(0)
                    ->formatStateUsing(fn (string $state): string =>  number_format($state, 2, ',', '.') . ' km')
                    ->sortable()
                    ->badge()

   TextColumn::make('rides_sum_distance')
                    ->label('Total distance')
                    ->sum('rides', 'distance')
                    ->default(0)
                    ->formatStateUsing(fn (string $state): string =>  number_format($state, 2, ',', '.') . ' km')
                    ->sortable()
                    ->badge()

The get colums method for the exporter looks like this:

   public static function getColumns(): array
    {
        return [
            ExportColumn::make('id')
                ->label('ID'),
            ExportColumn::make('name'),
            ExportColumn::make('email'),
            ExportColumn::make('created_at'),
            ExportColumn::make('rides_sum_distance')
                ->sum('rides', 'distance')
                ->default(0)
                ->formatStateUsing(fn (string $state): string => number_format($state, 2, ',', '.') . ' km '),
        ];
    }

   public static function getColumns(): array
    {
        return [
            ExportColumn::make('id')
                ->label('ID'),
            ExportColumn::make('name'),
            ExportColumn::make('email'),
            ExportColumn::make('created_at'),
            ExportColumn::make('rides_sum_distance')
                ->sum('rides', 'distance')
                ->default(0)
                ->formatStateUsing(fn (string $state): string => number_format($state, 2, ',', '.') . ' km '),
        ];
    }

this all works. the table shows the sum correctly and i can export it. however when i sort the table on the rides_sum_distance i get the following error for the PrepareCsvExport job:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rides_sum_distance' in 'order clause' (Connection: mysql, SQL: select distinct * from `users` order by `rides_sum_distance` desc limit 100 offset 0)

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rides_sum_distance' in 'order clause' (Connection: mysql, SQL: select distinct * from `users` order by `rides_sum_distance` desc limit 100 offset 0)

It seems like the moment a sort is added the export looks for the colomn in the database. Is there any way to prevent this ?

7 Replies

Tetracyclic•6mo ago

You can use modifyQueryUsing on the exporter action to get the query that will be executed. If you dump that you can inspect what's in the orders property and potentially drop that ordering if it exists. Though I'm not sure exactly why it doesn't work as is, I would have assumed the sum was being carried out in the original query and so it should be able to sort on that calculated column. But inspecting the query passed to modifyQueryUsing should give you some insight to why it is happening

kwatmanOP•6mo ago

I looked at the query object like you said and can see that there is an order added for rides_sum_distance. im able to overwrite it with:

public static function modifyQuery(Builder $query): Builder
{
    return $query->reorder();
}

public static function modifyQuery(Builder $query): Builder
{
    return $query->reorder();
}

I can then export it without problem. But this causes me to loose the ordering i want. I looked further into the query object but i don't really know what to look for.

Tetracyclic•6mo ago

You can do dd($query->toSql()) to get the query it's going to run, presumably it's missing the clause that would calculate that column

kwatmanOP•6mo ago

it indeed misses it.

select * from `users` order by `rides_sum_distance` desc

select * from `users` order by `rides_sum_distance` desc

Tetracyclic•6mo ago

As a temporary work around, you should be able to apply $query->withSum('rides', 'distance) to that query, but I'm not sure if that would break something else It gets added when exporting each column here: https://github.com/filamentphp/filament/blob/d00919cbac22c9b8648b114815ab635aaac6d509/packages/actions/src/Exports/ExportColumn.php#L117

kwatmanOP•6mo ago

the $query->withSum('rides', 'distance) fixed the problem. thanks for helping. But its weird that it is not doing it automatically for me.

Tetracyclic•6mo ago

It's definitely worth opening an issue about this, as the framework should take care of it, I imagine it's just an oversight. https://github.com/filamentphp/filament/issues/new/choose

Gaming

Programming

Exporting a sorted sum column with ExportAction