F
Filament•2mo ago
Arjan

Issue with Sorting a Summed Relationship Column in Filament Table

I have a Filament table displaying a list of my “Projects.” One of the columns calculates the sum of the “converted_net” values from the related “payments” table. This works correctly, and for projects without payments (i.e., nothing to sum), the column displays 0. However, sorting this column does not work as expected. When sorting in descending order, projects with no payments (null values) appear at the top instead of those with the highest payment sums. How can I fix this? 
                    TextColumn::make('payments_sum_converted_net')->sum([
                        'payments' => fn(Builder $query) => $query->whereNull('statement_id')->whereNotNull('received_at')->whereNotNull('converted_net'),
                    ], 'converted_net')
                        ->default(0)
                        ->money(fn(Project $record) => $record->currency->iso_code_3, divideBy: 100)
                        ->label('Payments')
                    TextColumn::make('payments_sum_converted_net')->sum([
                        'payments' => fn(Builder $query) => $query->whereNull('statement_id')->whereNotNull('received_at')->whereNotNull('converted_net'),
                    ], 'converted_net')
                        ->default(0)
                        ->money(fn(Project $record) => $record->currency->iso_code_3, divideBy: 100)
                        ->label('Payments')
1 Reply
Arjan
ArjanOP•2mo ago
I have this code now that works: 
TextColumn::make('payments_sum_converted_net')
                        ->sum([
                            'payments' => fn(Builder $query) => $query->whereNull('statement_id')
                                ->whereNotNull('received_at')
                                ->whereNotNull('converted_net'),
                        ], 'converted_net')
                        ->default(0)
                        ->money(fn(Project $record) => $record->currency->iso_code_3, divideBy: 100)
                        ->label('Payments')
                        ->sortable(query: function (Builder $query, string $direction): Builder {
                            // Create a simple expression for the sum and use it for sorting
                            $sumExpression = "(SELECT COALESCE(SUM(converted_net), 0) 
                                          FROM payments 
                                          WHERE projects.id = payments.project_id 
                                          AND statement_id IS NULL 
                                          AND received_at IS NOT NULL 
                                          AND converted_net IS NOT NULL)";

                            return $query->orderByRaw("{$sumExpression} {$direction}");
                        }),
TextColumn::make('payments_sum_converted_net')
                        ->sum([
                            'payments' => fn(Builder $query) => $query->whereNull('statement_id')
                                ->whereNotNull('received_at')
                                ->whereNotNull('converted_net'),
                        ], 'converted_net')
                        ->default(0)
                        ->money(fn(Project $record) => $record->currency->iso_code_3, divideBy: 100)
                        ->label('Payments')
                        ->sortable(query: function (Builder $query, string $direction): Builder {
                            // Create a simple expression for the sum and use it for sorting
                            $sumExpression = "(SELECT COALESCE(SUM(converted_net), 0) 
                                          FROM payments 
                                          WHERE projects.id = payments.project_id 
                                          AND statement_id IS NULL 
                                          AND received_at IS NOT NULL 
                                          AND converted_net IS NOT NULL)";

                            return $query->orderByRaw("{$sumExpression} {$direction}");
                        }),

Did you find this page helpful?