Filament•11mo ago

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.

1 Reply

Arlind MusliuOP•11mo 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();

Gaming

Programming

Group by date and summarize number of pages