Using groupBy in ListOperation - SELECT list is not in GROUP BY

[nrodriguezm]

Hi, I would like to list a model but aggregate by a column. Is this possible?

When I use the following in setupListOperation:

$this->crud->groupBy('generic_code');

I get the corresponding error as I am not setting columns to select:

Illuminate \ Database \ QueryException
PHP 8.2.1
9.52.15
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.materials.id' which is not functionally dependent on columns in GROUP BY clause;

Thanks for your help!

[nrodriguezm]

I think I made it work, after some tinkering I got the desired result with:

$this->crud->groupBy(['generic_code', 'materials.id'])->select([
'id', 'generic_code', 'material_number',
]);

Note the select method comes AFTER the groupBy!

[karandatwani92]

There is no such thing as "select method comes AFTER the groupBy!"
Laravel doc shows many examples where select is before groupBy
https://laravel.com/docs/10.x/queries#havingraw-orhavingraw

[nrodriguezm]

One more detail needed is to disable default ordering by primary key, column id in this case. This is needed to avoid grouping by id column, we dont want to group by id!

I've found this issue from 2021 but is closed,
Laravel-Backpack/CRUD#3421

When using a custom orderBy:

$this->crud->orderBy('generic_code');

We get the following SQL error:

Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

This is because the primary key has been added after the custom orderBy()? Is this expected?

generic_code,
container_id
from
materials
group by
container_id,
generic_code
order by
generic_code asc,
id desc <<< This!

Edit, more recent discussion:
Laravel-Backpack/docs#380

[karandatwani92]

Hey @nrodriguezm
Assuming you are on MaterialCRUD and generic_code, container_id are columns of materials table.

To perform similarly, I tried groupBy & order on the demo's monster table.

public function setupListOperation(){
 $this->crud->groupBy(['email', 'number']);
 $this->crud->orderBy('email');
 //rest of the code.
}

It simply worked... Let me know if I'm missing something.