Aggregate data in table footer (sum, avg...etc)
In many of the tables we use, we sometimes need to calculate the sum, average, maximum, minimum, or count for certain columns and display the data in the table footer.
To do so, we can use the getTableContentFooter()
function and a custom view.
sum
function.<x-tables::row> <x-tables::cell> {{-- for the checkbox column --}} </x-tables::cell> @foreach ($columns as $column) <x-tables::cell wire:loading.remove.delay wire:target="{{ implode(',', \Filament\Tables\Table::LOADING_TARGETS) }}" > @for ($i = 0; $i < count($calc_columns); $i++ ) @if ($column->getName() == $calc_columns[$i]) <div class="filament-tables-column-wrapper"> <div class="filament-tables-text-column px-4 py-2 flex w-full justify-start text-start"> <div class="inline-flex items-center space-x-1 rtl:space-x-reverse"> <span class="font-medium"> {{ $records->sum($calc_columns[$i]) }} </span> </div> </div> </div> @endif @endfor </x-tables::cell> @endforeach</x-tables::row>
Here, we loop through the columns and render a table cell for each one. If the column name matches one of the columns we want to aggregate, we calculate the sum.
ListResourceExample
page where you want to apply this footer (or in ExampleRelationManager if you wish to use it in relation table), add the following code to specify the columns to aggregate:public array $data_list= [ 'calc_columns' => [ 'column1', 'column2', ],];
getTableContentFooter()
function and specify the view we just created and pass the columns we want to aggregate:protected function getTableContentFooter(): ?View{ return view('table.footer', $this->data_list);}
Hi Moaaz Faracheh, Thanks for the neat tip. Here is a typo: {{ $records->sum($calc_columns[$I]) }}. Should be $i. greetings, martin
Hi Moaaz Faracheh, great trick! Thanks!
For my use case, I expanded your code to accept some parameters like format (money) and currency, as well as the case where the table does not have a checkbox column.
See:
Martin Mildner Thanks alot, I will correct it.
Sergio Jardim Thats really great thanks alot for the contribute.
Hay moaaz faracheh Can we use that similar method to count peak values of widget charts ? Because I tried doing that it gives me an error.Ty
Hello Diksha, you cannot use this exact code for the widgets i will comment below how I can apporach such thing.
This how approched it,
Instead of
{{$money ? 'justify-end text-end' : 'justify-start text-start'}}
you could use:$columnClasses = match($column->getAlignment()) { 'center' => 'justify-center text-center', 'end' => 'justify-end text-end', 'left' => 'justify-start text-left', 'right' => 'justify-end text-right', 'justify' => 'justify-between text-justify', default => 'justify-start text-start', };
and{{ $columnClasses }}
For adding the aggregate functions to the widgets. Here I took the ordersChart function from the demo and made the modifications so you can apply it. In the widget you need to add such thing in the
getHeading()
function there is not footer as in tables. hope this helps.@Diksha
@moaazfaracheh - Thank-you so much but just wanna know one more thing, Can I do this using map function ? I want to calculate Total number or sum of Peaks using line graph chart. Right now I setup the data array and Declare number of peaks variable and set it to zero & Loop through each element of the data array and add the value to the total and then I set the message property & Return the data for the chart . But using this in return datasets' => [ [ 'label' => 'Test Data By Month', 'data' => $data, ], ], 'labels' => ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'], ]; CAN I DO THE ABOVE WITH THIS DATASET? -> 'datasets' => [ [ 'label' => 'Prospects', 'data' => $data->map(fn (TrendValue $value) => $value->aggregate), ], ], 'labels' => $data->map(fn (TrendValue $value) => $value->date), ];
most welcome, one thing I am not getting where are you trying to get the $data from ? is it just one table in the database?
yes @moaazfaracheh
public $message = '';
} This is the code I used.
@moaazfaracheh But I want It to be done using this return [ 'datasets' => [ [ 'label' => 'Prospects', 'data' => $data->map(fn (TrendValue $value) => $value->aggregate), ], ], 'labels' => $data->map(fn (TrendValue $value) => $value->date), ]; }
Hi again Diksha I hope this code is your approach (make sure to install the
flowframe/laravel-trend
package)change the
Order::class
to the model you wish to use. thetotal_price
is the column name i want to get the sum for. in theOrder::class
i havecreated_at
column also which is used here to give the correct data.documentation.
https://filamentphp.com/docs/2.x/admin/dashboard/charts#generating-chart-data-from-an-eloquent-model
@moaazfaracheh than-you so much :)
Can i use this on a table widget? Thanks moaaz faracheh
Hello Garadiit, the trick is for using aggregate in the footer. but you can check my last comment where it has code, it's for widgets. let me know if it help.
What would it take to use a calculated field? I wrote a Discord question with some detail (https://discord.com/channels/883083792112300104/1131256536656773172)
Tables\Columns\TextColumn::make('id') ->getStateUsing(function(Schedule $record) { return $record->id; }), Tables\Columns\TextColumn::make('id_test') ->getStateUsing(function(Schedule $record) { return $record->id; }),
Cannot paste image..
Nice trick!
How can i find count the working_day ( date ) column ?
im getting SUM() of first 10 item of pagination
I've modified the code a little. Now it counts all records without depending on the page.
And view