Doctrine generates incorrect SQL when combining aggregate fields (group by) and ordering (order by) in a query with Paginator

Question

I have a simple bi-directional one-to-many relationship between Item and Valoracion (a review). The following query should get the average score and number of reviews for each Item, in descending order:

$itemsQb = $em->getRepository(Item::class)->createQueryBuilder('i')
    ->select('i as data')
    ->addSelect('avg(v.score) as avg_score')
    ->addSelect('count(v.score) as num_reviews')
    ->leftJoin('i.valoraciones', 'v')
    ->groupBy('i.id')
    ->addOrderBy('avg_score', 'DESC')
    ->addOrderBy('num_reviews', 'DESC');

where $em is a working Doctrine\ORM\EntityManager instance. When paginating the above mentioned query with Doctrine\ORM\Tools\Pagination\Paginator and traversing the results using getIterator() an exception is thrown, as follows:

$pag = new Paginator($itemsQb);

// first page, up to three results per page
$pag->getQuery()->setFirstResult(0)->setMaxResults(3);

// display results one by one
echo "Name\t\tAvg\tNum\n";
foreach ($pag->getIterator() as $p) {
   echo $p['data']->name . "\t" . $p['avg_score'] . "\t" . $p['num_reviews'] . "\n";
}

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.v1_.score' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The following SQL query was generated

SELECT DISTINCT
id_5
FROM
(SELECT DISTINCT
    id_5, sclr_2, sclr_3
FROM
    (SELECT 
    i0_.id AS id_0,
        i0_.name AS name_1,
        AVG(v1_.score) AS sclr_2,
        COUNT(v1_.score) AS sclr_3,
        v1_.score AS score_4,
        i0_.id AS id_5
FROM
    item i0_
LEFT JOIN valoracion v1_ ON i0_.id = v1_.item_id
GROUP BY i0_.id) dctrn_result_inner
ORDER BY sclr_2 DESC , sclr_3 DESC) dctrn_result
LIMIT 3

where it's obvious that the line v1_.score AS score_4, should't be there at all!

So, why is this invalid SQL being generated? Am I doing something wrong?

Notes:

• If using getQuery()->getResult() instead of getIterator() everything works fine. I am still asking for help as Twig apparently uses getIterator() behind the for loop when $pag is passed to the template.
• If the ORDER BY clauses are removed, everything works fine too!
• I am using MySQL 5.7.25, sql_mode=ONLY_FULL_GROUP_BY and I DON'T want to change it.

Answer 1

In this case, you do not fetch the joined "to-many collection", you group by the Item id and just aggregate values of the "valoraciones" relation.

According to doctrine pagination documentation, in this case you can disable the $fetchJoinCollection flag:

$pag = new Paginator($itemsQb, false);

It seems the issue is caused by doctrine trying to add the "missing fields" from the order by condition to the select clause. Related issue Pull request

In this case, it thinks it should add "v.score" to the select.

Alternatively, you could disable the use of output walkers in the paginator to avoid the above behavior:

$pag->setUseOutputWalkers(false);