PowerGrid + PostgreSQL: “column must appear in the GROUP BY clause” when aggregating by month

I'm using Laravel Livewire PowerGrid to display aggregated data by month from my events table (PostgreSQL).

I want to calculate monthly sums:

    public function datasource(): Builder  
    {  
        return DB::table('events')  
            ->whereNotNull('date')  
            ->selectRaw("  
                MIN(id) as id,  
                to_char(date, 'YYYY-MM') as month_key,  
                ROUND(SUM(COALESCE(total_cost, 0))::numeric, 2) as total_cost_sum,  
                ROUND(SUM(COALESCE(implementation_1c, 0))::numeric, 2) as implementation_sum  
            ")  
            ->groupByRaw("to_char(date, 'YYYY-MM')")  
            ->orderByRaw("to_char(date, 'YYYY-MM') DESC");  
    }

In my PowerGrid component, I set:

public string $primaryKey = 'month_key';

And I define the columns via transform() and fields(). When loading the table, I get this PostgreSQL error:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "events.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ... 'YYYY-MM') order by to_char(date, 'YYYY-MM') DESC, "id" asc ^ (Connection: pgsql, SQL: select count(*) as aggregate from "events" where "date" is not null group by to_char(date, 'YYYY-MM') order by to_char(date, 'YYYY-MM') DESC, "id" asc) 

What I tried:

• Added MIN(id) as id

• Set primaryKey = 'month_key' Additional questions:

Questions:

• Are there better alternatives to PowerGrid for displaying aggregated data with Laravel Livewire, especially when dealing with PostgreSQL and monthly summaries?

• In general, is it considered good practice to aggregate data on the fly like this in the datasource, or should I use a VIEW or MATERIALIZED VIEW for performance and reliability?

Full code:

final class EventsMonthlyTable extends PowerGridComponent
{
    public string $tableName = 'events-monthly-table';

    public string $primaryKey = 'month_key';

    public function setUp(): array
    {
        return [
            PowerGrid::header()
                ->showSearchInput(),

            PowerGrid::footer()
                ->showPerPage(false)
                ->showRecordCount(),

            PowerGrid::detail()
                ->view('components.events.monthly-detail')
                ->showCollapseIcon()
                ->params(['stub' => true]),
        ];
    }

    public function datasource(): Builder
    {
        return DB::table('events')
            ->whereNotNull('date')
            ->selectRaw("
                MIN(id) as id,
                to_char(date, 'YYYY-MM') as month_key,
                ROUND(SUM(COALESCE(total_cost, 0))::numeric, 2) as total_cost_sum,
                ROUND(SUM(COALESCE(implementation_1c, 0))::numeric, 2) as implementation_sum
            ")
            ->groupByRaw("to_char(date, 'YYYY-MM')")
            ->orderByRaw("to_char(date, 'YYYY-MM') DESC");
    }

    public function fields(): PowerGridFields
    {
        return PowerGrid::fields()
            ->add('month')
            ->add('total_cost_formatted')
            ->add('implementation_formatted');
    }

    public function columns(): array
    {
        return [
            Column::make('month', 'month')
                ->sortable(),

            Column::make('total_cost_formatted', 'total_cost_formatted')
                ->sortable(),

            Column::make('implementation_formatted', 'implementation_formatted')
                ->sortable(),
        ];
    }

    public function transform($row): array
    {
        return [
            'month' => Carbon::createFromFormat('Y-m', $row->month_key)
                ->translatedFormat('F Y'),

            'total_cost_formatted' => $this->formatMoney($row->total_cost_sum),

            'implementation_formatted' => $this->formatMoney($row->implementation_sum),
        ];
    }

    private function formatMoney(float $value): string
    {
        return number_format($value, 2, ',', ' ');
    }
}