F
Filament•2mo ago
Emruardo

Grouping month year and group summarize (total amount)

I couldn't figure out what I do wrong. grouping expenses by month-year works but when I want to show summarize on each month, sum is wrong Here is my Code 
---
Tables\Columns\TextColumn::make('amount')
  ->label('Amount')
  ->money('EUR')
  ->sortable()
  ->summarize(
      Tables\Columns\Summarizers\Sum::make()->label('Total')->money('EUR', divideBy: 100)
  ),
---
->groups([
    Group::make('date')
        ->getTitleFromRecordUsing(
            fn(Expense $record): string => Carbon::parse($record->date)->format('F Y')
        )
        ->orderQueryUsing(
            fn(Builder $query, string $direction) => $query->orderBy('date', $direction)
        )
        ->titlePrefixedWithLabel(false)
        ->collapsible(), 
])
---
Tables\Columns\TextColumn::make('amount')
  ->label('Amount')
  ->money('EUR')
  ->sortable()
  ->summarize(
      Tables\Columns\Summarizers\Sum::make()->label('Total')->money('EUR', divideBy: 100)
  ),
---
->groups([
    Group::make('date')
        ->getTitleFromRecordUsing(
            fn(Expense $record): string => Carbon::parse($record->date)->format('F Y')
        )
        ->orderQueryUsing(
            fn(Builder $query, string $direction) => $query->orderBy('date', $direction)
        )
        ->titlePrefixedWithLabel(false)
        ->collapsible(), 
])
No description
7 Replies
toeknee
toeknee•2mo ago
Seems to only be getting the last value. What does your model look like?
Emruardo
EmruardoOP•2mo ago
@toeknee sorry for the late reply, I've not noticed your message. Yes, summarize gets only last day's amount (in the list), but if there are more entries on the same day, sum of the entries will be showed 
class Expense extends Model
{
    use HasFactory;

    protected $casts = [
        'amount' => MoneyCast::class,
    ];
    //
    protected $fillable = [
        'date',
        'amount',
        'reason',
    ];
}
class Expense extends Model
{
    use HasFactory;

    protected $casts = [
        'amount' => MoneyCast::class,
    ];
    //
    protected $fillable = [
        'date',
        'amount',
        'reason',
    ];
}
No description
toeknee
toeknee•2mo ago
So strange, do you have a scope on your model? I suggest removing this package, it contains your .env file which contains your database password too...
Dennis Koch
Dennis Koch•2mo ago
I deleted the message
toeknee
toeknee•2mo ago
@Emruardo Ok so the issue is down to how you have built the group. You are grouping by date so the last total is the last date unless there is multiple on that date. It becomes messy. So instead create a virtual column as your using mysql: 
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table('expenses', function (Blueprint $table) {
            // create a virtual column for month-year date from date
            $table->string('month_date')->virtualAs('DATE_FORMAT(date, "%Y-%m")');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('expenses', function (Blueprint $table) {
            $table->dropColumn('month_date');
        });
    }
};
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table('expenses', function (Blueprint $table) {
            // create a virtual column for month-year date from date
            $table->string('month_date')->virtualAs('DATE_FORMAT(date, "%Y-%m")');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('expenses', function (Blueprint $table) {
            $table->dropColumn('month_date');
        });
    }
};
then use month_date instead of date and jobs a goodun.
Emruardo
EmruardoOP•2mo ago
@toeknee Thanks a lot. Not only for the solution, at the same time I learned something new. *password in .env nowhere is used (changed before uploading), but thank you for concern
bionary
bionary•2w ago
Thank you dearly for this. I can now go put some ice on the bruises on my head!

Did you find this page helpful?