Filament•16mo ago

Error When Displaying Aggregated Data in Filament Resource Table

Hello everyone, I need assistance with an issue, please. I have a model that requires data aggregation (calculating sums by group). Afterwards, I'd like to display these aggregated data in a resource table. To transform the data, I'm using Eloquent:

return $table->modifyQueryUsing(function (Builder $query) use ($today) {
        $query->where('dt', '>=', $today)
            ->groupBy('hour')
            ->selectRaw('DATE_FORMAT(dt, "%H:00") as hour, SUM(rounded) as total_sum');
    })

    ->columns([
        Tables\Columns\TextColumn::make('hour'),
        Tables\Columns\TextColumn::make('total_sum'),
    ]);

However, I'm encountering an error: "Filament\Resources\Pages\ListRecords::getTableRecordKey(): Return value must be of type string, null returned." I understand that ListRecords requires an ID value from the model's record. However, after data transformation, this ID value is no longer present. Could you please advise on how to solve this issue or suggest a workaround?

2 Replies

Arlind Musliu•11mo ago

Hey @Oleksandr , did you manage to fix this? I have the same problem and error 😅 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:

->where('dt', '>=', $today)
->groupByRaw('DATE_FORMAT(dt, "%H:00")')
->selectRaw('ROW_NUMBER() OVER (ORDER BY hour) AS id')
->selectRaw('DATE_FORMAT(dt, "%H:00") as hour, SUM(rounded) as total_sum');

->where('dt', '>=', $today)
->groupByRaw('DATE_FORMAT(dt, "%H:00")')
->selectRaw('ROW_NUMBER() OVER (ORDER BY hour) AS id')
->selectRaw('DATE_FORMAT(dt, "%H:00") as hour, SUM(rounded) as total_sum');

I have modified my code to adapt it to your need, but you'll have to test it.

Fasna•8mo ago

Thank you @Arlind Musliu , you saved my day

Gaming

Programming

Error When Displaying Aggregated Data in Filament Resource Table