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, ',', ' '); } }