Filament•4w ago

Group Table By Year

Hi all I have an Invoice model and I want to show a table of profits per year. The column is 'date' and 'profit' so I need to YEAR(date) and SUM(profit). Ive had no end of trouble trying all sorts of methods. Does anyone know how to do this ?

1 Reply

David | Fortune ValidatorOP•4w ago

So I have managed the following with looking through a couple of other help posts. Is there a most Filament way of doing this though:

    public function table(Table $table): Table
    {
        return $table
            ->modifyQueryUsing(fn($query) =>
                $query
                    ->selectRaw('ROW_NUMBER() OVER (ORDER BY DATE_FORMAT(date, "%Y")) AS id')
                    ->selectRaw('YEAR(date) as year,SUM(sub_total) as turnover, SUM(profit) as profit,SUM(baProfit) as benProfit,SUM(budgetProfit) as budgetProfit')
                    ->groupByRaw('YEAR(date)') // Group by year
                    ->orderBy('year', 'desc') // Sort by year
            )
            ->groupsOnly()
            ->columns([
                Tables\Columns\TextColumn::make('year')
                    ->label('Year')
                    ->sortable(),
                Tables\Columns\TextColumn::make('turnover')->numeric(0)
                    ->sortable(),
                Tables\Columns\TextColumn::make('profit')->numeric(0)
                    ->sortable(),
                Tables\Columns\TextColumn::make('benProfit')->numeric(0)
                    ->sortable(),
                Tables\Columns\TextColumn::make('budgetProfit')->numeric(0)
                    ->sortable(),
            ]);
    }

    public function table(Table $table): Table
    {
        return $table
            ->modifyQueryUsing(fn($query) =>
                $query
                    ->selectRaw('ROW_NUMBER() OVER (ORDER BY DATE_FORMAT(date, "%Y")) AS id')
                    ->selectRaw('YEAR(date) as year,SUM(sub_total) as turnover, SUM(profit) as profit,SUM(baProfit) as benProfit,SUM(budgetProfit) as budgetProfit')
                    ->groupByRaw('YEAR(date)') // Group by year
                    ->orderBy('year', 'desc') // Sort by year
            )
            ->groupsOnly()
            ->columns([
                Tables\Columns\TextColumn::make('year')
                    ->label('Year')
                    ->sortable(),
                Tables\Columns\TextColumn::make('turnover')->numeric(0)
                    ->sortable(),
                Tables\Columns\TextColumn::make('profit')->numeric(0)
                    ->sortable(),
                Tables\Columns\TextColumn::make('benProfit')->numeric(0)
                    ->sortable(),
                Tables\Columns\TextColumn::make('budgetProfit')->numeric(0)
                    ->sortable(),
            ]);
    }

Gaming

Programming

Group Table By Year