SQL error opening lists page

[iraldoad]

Hello. I generated a CRUD for an entity with two fields and when I try to access the list it throws me the following error.

SQLSTATE[42803]: Grouping error: 7 ERROR: la columna «n0_.id» debe aparecer en la cláusula GROUP BY o ser usada en una función de agregación

I'm using PHP 7.0.10, Symfony 3.3.2 and PostgreSQL 9.4.

[iraldoad]

[petkopara]

Hi @iraldoad, In some databases like postgres and oracle all selected columns must appear in the GROUP BY clause or be used in an aggregate function. Possible solution is to add these group by clauses directly to the generated code until I find some workaround.

[victoracho]

I got the same issue , what could I do to fix it ?

[petkopara]

Hi @victoracho, except the one above

add these group by clauses directly to the generated code

which means in the totals query

$totalOfRecords = $queryBuilder->select('COUNT(e.{{identifier}})')->getQuery()->getSingleScalarResult();

to add all of the selected fields, which are in the default case all fields of the entity.

$totalOfRecords = $queryBuilder->select('COUNT(e.{{identifier}})')->groupBy('id')->addGroupBy('name') .. ->addGroupBy('theLastColName') ->getQuery()->getSingleScalarResult();

Or you could just remove all $totalOfRecords occurences in the controller and generated index.html.twig.

Or If you don't want to do it manually for all generated files, you could extend index.html.twig and controller of the bundle and remove them there.

[victoracho]

Thank you, I just deleted all of the totalofrecords occurrences and it worked, but the delete action seems to be blocked or something, will the bundle work normally without the things i just erased ?

[MatthieuSarter]

Hi @petkopara,

Adding all the fields to the GROUP BY does not work for me, as the request now returns multiple results (one for each entry in the table, due to the grouping by id...) : "The query returned multiple rows. Change the query or use a different result function like getScalarResult()."

Since the issue is indeed caused by the ORDER BY clause added when calling pagination(), the solution is to call getTotalOfRecordsString() before calling pagination() in indexAction() :

        list($filterForm, $queryBuilder) = $this->filter($queryBuilder, $request);
        $totalOfRecordsString = $this->getTotalOfRecordsString(clone $queryBuilder, $request);
        list($projects, $pagerHtml) = $this->paginator($queryBuilder, $request);

Note that it is required to clone the $queryBuilder. Otherwise, there will be an error in the paginator ("The Paginator does not support Queries which only yield ScalarResults.").