F
Filament•12mo ago
Arlind Musliu

Group by date and summarize number of pages

I have a table that records each book read, the user, number of pages read and the date. I have used summarize for displaying the total number of pages read and also used ->defaultGroup to group by month and year. This looks great, but the summarize result is not correct. 
return $table
            ->columns([
                Tables\Columns\TextColumn::make('user.name')
                    ->label('Reader')
                    ->searchable(),
                Tables\Columns\TextColumn::make('book.title')
                    ->label('Book')
                    ->searchable(),
                Tables\Columns\TextColumn::make('book.author.name')
                    ->label('Author')
                    ->searchable(),
                Tables\Columns\TextColumn::make('date')
                    ->date('M Y')
                    ->searchable(),
                Tables\Columns\TextColumn::make('pages')
                ->summarize(Sum::make('pages')),
            ])
            ->defaultGroup(
                Group::make('date')
                    ->getTitleFromRecordUsing(fn(Reading $record): string => $record->date->format('M Y'))
                    ->orderQueryUsing(fn(Builder $query, string $direction) => $query->orderBy('date', 'desc'))
                    ->collapsible(),
            )
return $table
            ->columns([
                Tables\Columns\TextColumn::make('user.name')
                    ->label('Reader')
                    ->searchable(),
                Tables\Columns\TextColumn::make('book.title')
                    ->label('Book')
                    ->searchable(),
                Tables\Columns\TextColumn::make('book.author.name')
                    ->label('Author')
                    ->searchable(),
                Tables\Columns\TextColumn::make('date')
                    ->date('M Y')
                    ->searchable(),
                Tables\Columns\TextColumn::make('pages')
                ->summarize(Sum::make('pages')),
            ])
            ->defaultGroup(
                Group::make('date')
                    ->getTitleFromRecordUsing(fn(Reading $record): string => $record->date->format('M Y'))
                    ->orderQueryUsing(fn(Builder $query, string $direction) => $query->orderBy('date', 'desc'))
                    ->collapsible(),
            )
As you can see on Date: Dec 2020 the sum of pages shows 243, which is not correct.
No description
2 Replies
Arlind Musliu
Arlind MusliuOP•12mo ago
I found a solution for my issue: The problem when grouping is that Filament expects an ID column when displaying the data in a table. However, as we know, when we group rows according to a particular field, we don't need the ID of a row. To fix this, we need to create a new field ID that will be formed by the number of total grouped roles. That can be done through SQL like this: 
return $table->query(
Reading::query()
        ->groupByRaw('DATE_FORMAT(date, "%Y-%m")')
        ->selectRaw('ROW_NUMBER() OVER (ORDER BY DATE_FORMAT(date, "%Y-%m")) AS id')
        ->selectRaw('DATE_FORMAT(date, "%Y-%m") as date, SUM(pages) as pages')
        ->orderBy('date', 'desc')
        ->where('user_id', auth()->user()->id)
        ->limit(10)
    )
        ->columns([
Tables\Columns\TextColumn::make('date')
        ->label('Month')
        ->date('M Y'),
Tables\Columns\TextColumn::make('pages')
         ->label('Pages Read'),
            ])
        ->groupsOnly();
return $table->query(
Reading::query()
        ->groupByRaw('DATE_FORMAT(date, "%Y-%m")')
        ->selectRaw('ROW_NUMBER() OVER (ORDER BY DATE_FORMAT(date, "%Y-%m")) AS id')
        ->selectRaw('DATE_FORMAT(date, "%Y-%m") as date, SUM(pages) as pages')
        ->orderBy('date', 'desc')
        ->where('user_id', auth()->user()->id)
        ->limit(10)
    )
        ->columns([
Tables\Columns\TextColumn::make('date')
        ->label('Month')
        ->date('M Y'),
Tables\Columns\TextColumn::make('pages')
         ->label('Pages Read'),
            ])
        ->groupsOnly();
David | Fortune Validator
David | Fortune Validator•5w ago
Hi, I hope you dont mind me jumping on this: I wanted to group by Year only. 
 public function table(Table $table): Table
    {
        return $table->query(
            Invoice::query()
                ->groupByRaw('DATE_FORMAT(date, "%Y")')
                ->selectRaw('ROW_NUMBER() OVER (ORDER BY DATE_FORMAT(date, "%Y")) AS id')
                ->selectRaw('DATE_FORMAT(date, "%Y") as date, SUM(profit) as pages')
                ->orderBy('date', 'desc')
                ->where('client_id', $this->getOwnerRecord()->id)
                ->limit(10)
        )
            ->columns([
                Tables\Columns\TextColumn::make('date')
                    ->label('Year')
                    ->date('Y'),
                Tables\Columns\TextColumn::make('pages')
                    ->label('Pages Read'),
            ])
            ->groupsOnly();


    }
 public function table(Table $table): Table
    {
        return $table->query(
            Invoice::query()
                ->groupByRaw('DATE_FORMAT(date, "%Y")')
                ->selectRaw('ROW_NUMBER() OVER (ORDER BY DATE_FORMAT(date, "%Y")) AS id')
                ->selectRaw('DATE_FORMAT(date, "%Y") as date, SUM(profit) as pages')
                ->orderBy('date', 'desc')
                ->where('client_id', $this->getOwnerRecord()->id)
                ->limit(10)
        )
            ->columns([
                Tables\Columns\TextColumn::make('date')
                    ->label('Year')
                    ->date('Y'),
                Tables\Columns\TextColumn::make('pages')
                    ->label('Pages Read'),
            ])
            ->groupsOnly();


    }
the sum works but the date column shows the same year in each row. any ideas?

Did you find this page helpful?