Filament•2mo ago

Custom Aggregate Table

Hi. I'm having error on aggregate table on v4. In v3, I can add the getTableRecordKey function and everything work just fine. But in v4, even though I added this to resolve table record id, but it seems like the query builder still get the model id in the groupBy by the end. Here is what i have on v4 custom table page:

class CustomPage extends Page implements HasTable
{
    use InteractWithTable;
    ...
    
    protected function getTableQuery(): Builder
    {
        return CustomsData::query()
            ->select('importer')
            ->selectRaw('COUNT(product) as total_import')
            ->selectRaw('SUM(qty) as total_qty')
            ->selectRaw('SUM(value) as total_value')
            ->groupBy('importer');
    }

    public function table....
    ...

    public function getTableRecordKey(Model|array $record): string
    {
        return uniqid();
    }
}

class CustomPage extends Page implements HasTable
{
    use InteractWithTable;
    ...
    
    protected function getTableQuery(): Builder
    {
        return CustomsData::query()
            ->select('importer')
            ->selectRaw('COUNT(product) as total_import')
            ->selectRaw('SUM(qty) as total_qty')
            ->selectRaw('SUM(value) as total_value')
            ->groupBy('importer');
    }

    public function table....
    ...

    public function getTableRecordKey(Model|array $record): string
    {
        return uniqid();
    }
}

and here is the page error it throws:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'customs_data.customs_data.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (Connection: mysql_customs_data, SQL: select `importer`, COUNT(product) as total_import, SUM(qty) as total_qty, SUM(value) as total_value from `customs_data` where (date(`import_date`) >= 2025-07-08 and date(`import_date`) <= 2025-10-08) group by `importer` order by `total_value` desc, `customs_data`.`id` asc limit 10 offset 0)

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'customs_data.customs_data.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (Connection: mysql_customs_data, SQL: select `importer`, COUNT(product) as total_import, SUM(qty) as total_qty, SUM(value) as total_value from `customs_data` where (date(`import_date`) >= 2025-07-08 and date(`import_date`) <= 2025-10-08) group by `importer` order by `total_value` desc, `customs_data`.`id` asc limit 10 offset 0)

Can any body had any solution for this?

15 Replies

Dennis Koch•2mo ago

Does this work?

->groupBy(['importer', 'id']);

->groupBy(['importer', 'id']);

Hung ThaiOP•2mo ago

thanks for your reply. This did render the page just fine. But then, it destroy the purpose of the page. I want to make a report page by grouping by some column.

Dennis Koch•2mo ago

Isn't it grouped by importer now? Not sure when the ID is added. You could try disabling only_full_group_by from sql_mode.

Hung ThaiOP•2mo ago

Only when I added customs_data.id, the page runs. But it doesn't grouped by importer only, which destroyed the purpose of the page. I'm pretty sure the page worked just fine in V3 (similar query), but when upgrade to V4, it throws the error.

pocket.racer•4w ago

having same problem here. in v3 it works totally fine. in v4 I get something like this

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'my_db.donations.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'my_db.donations.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

happening in my dashboard widget tables with group by

Dennis Koch•4w ago

I think you can disable this behaviour by ->hasDefaultKeySort(false) on the table.

pocket.racer•4w ago

so i apply it on every widget table in v4?

Dennis Koch•4w ago

Probably everywhere where you use GROUP BY and use MySQLs ONLY_FULL_GROUP_BY

pocket.racer•4w ago

i see. ok will try and see if that fixes it Strange that i got no errors in v3 tho i just checked out ->hasDefaultKeySort() It doesn't accept any argument, so i cannot pass false into it

Dennis Koch•4w ago

Ah, it's ->defaultKeySort(false)

pocket.racer•4w ago

ah so in v3 it is false and in v4 it is true by default? Actually just curious, what does it do and how is it different from ->defaultSort('id', 'desc')

Dennis Koch•4w ago

I am not 100% sure, but I assume we add a default sort on the key of the model, so ordering is consistent. Maybe that wasn't the case in v3. Probably no difference to ->defaultSort('id', 'desc'), just that it's applied automatically if you don't define any sort

Hung ThaiOP•4w ago

No. The query always added "id" sort by the end. Even if I tried to default to a different column. For example, ->defaultSort('created_at') will always result in created_at asc and id asc in SQL output. I don't know why this behavior is enabled by default, but for this reason, every time I want to make something run smoother, I have to create both the columns I want to index, plus the id.

Dennis Koch•4w ago

Yes, but I wrote ->defaultKeySort(false). Did you try that one?

Hung ThaiOP•4w ago

I will try that later. Sorry, I though you were discussing about adding ->defaultSort() will remove the 'id' sort by default.

Gaming

Programming

Custom Aggregate Table

Did you find this page helpful?