Table Grouping and Summarizers

Filament also offers convenient ways to group table data by columns and calculate the aggregated data like "sum" or "average" to show at the bottom.

To demonstrate that, I've created a new Model for Orders:

app/Models/Order.php:

class Order extends Model
{
    use HasFactory;
 
    protected $fillable = ['user_id', 'product_id', 'price'];
 
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }
 
    public function product(): BelongsTo
    {
        return $this->belongsTo(Product::class);
    }
}

And seeded some data for the orders:

Then, we can generate a new Filament Resource to show the table of Orders:

php artisan make:filament-resource Order

The table has these values:

app/Filament/Resources/OrderResource.php:

class OrderResource extends Resource
{
    // ...
 
    public static function table(Table $table): Table
    {
        return $table
            ->columns([
                Tables\Columns\TextColumn::make('created_at')
                    ->dateTime(),
                Tables\Columns\TextColumn::make('product.name'),
                Tables\Columns\TextColumn::make('user.name'),
                Tables\Columns\TextColumn::make('price')
                    ->money('usd')
                    ->getStateUsing(function (Order $record): float {
                        return $record->price / 100;
                    })
            ])
            ->defaultSort('created_at', 'desc')
            // ... other methods with default values
    }
}

Here's how it looks now:

Grouping by Product Name

What if you want to divide this table by some condition? Let's try to group by product. Then, you just add a ->defaultGroup() method.

return $table
    ->columns([
        Tables\Columns\TextColumn::make('created_at')
            ->dateTime(),
        Tables\Columns\TextColumn::make('product.name'),
        Tables\Columns\TextColumn::make('user.name'),
        Tables\Columns\TextColumn::make('price')
            ->money('usd')
            ->getStateUsing(function (Order $record): float {
                return $record->price / 100;
            })
    ])
    ->defaultSort('created_at', 'desc')
    ->defaultGroup('product.name')

Here's the result:

Cool, right?

Table/Group Summary: Total Order Price

Another powerful concept is to have a sum/average row at the bottom, to aggregate the data from the table.

In Filament, they are called Summarizers.

In each table column, we can add ->summarize() to the chain and provide the rules for summarizing.

Let's try it without grouping first:

return $table
    ->columns([
        // ... other columns
        Tables\Columns\TextColumn::make('price')
            ->money('usd')
            ->getStateUsing(function (Order $record): float {
                return $record->price / 100;
            })
            ->summarize(Tables\Columns\Summarizers\Sum::make()), 
    ])
    // Commented out grouping, for now
    // ->defaultGroup('product.name')

Here's how it looks.

You probably have noticed that the sum is incorrect, as our DB contains price values in cents, so we need to divide it by 100. This is the syntax:

Tables\Columns\TextColumn::make('price')
    ->money('usd')
    ->getStateUsing(function (Order $record): float {
        return $record->price / 100;
    })
    ->summarize(Tables\Columns\Summarizers\Sum::make()
        ->formatStateUsing(fn ($state) => '$' . number_format($state / 100, 2))
    ),

Looks better now:

Now, if we combine the summarizers with the groups we saw above, Filament will show the summary for both the group and the table.

return $table
    ->columns([
        // ...
        Tables\Columns\TextColumn::make('price')
            ->money('usd')
            ->getStateUsing(function (Order $record): float {
                return $record->price / 100;
            })
            ->summarize(Tables\Columns\Summarizers\Sum::make()
                ->formatStateUsing(fn ($state) => '$' . number_format($state / 100, 2))
            ),
    ])
    ->defaultGroup('product.name')

Here's the result:

And also, it still shows the total sum at the bottom:

Interestingly, Filament optimizes SQL queries under the hood, so there's only one query for grouped summary and another for the total summary. There's no N+1 query problem.

If we take a look at the Debugbar, we see these two queries:

select sum(orders.price) as "PWpinat1Bw0cknFP",
    products.name as "PSfJ4eWj4cHYpN2f"
         from (select * from `orders`) as `orders`
             left join `products`
                  on `orders`.`product_id` = `products`.`id`
              group by `products`.`name`
 
select sum(orders.price) as "PWpinat1Bw0cknFP"
    from (select * from `orders`) as `orders`

Filament ships with four types of summarizers:

• Average
• Count
• Range
• Sum

You can read more about them in the official docs.