4

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.
Rafael Aquino
  • 45
  • 6

1 Answers1

2

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);
Jannes Botis
  • 11,154
  • 3
  • 21
  • 39
  • Initializing paginator with `$fetchJoinCollection = false` did the trick! The iterator now works perfectly. – Rafael Aquino Mar 07 '19 at 12:45
  • As a side-note: In Twig, apparently if iterating through the result of `getQuery()->getResult()`, while it contains the current page elements, the `loop` variables will contain unexpected values; `loop.length` will give the total number of elements instead of the element count for the current page; and `loop.last` will not be true for the last element of the collection, not for the last element of the page. – Rafael Aquino Mar 07 '19 at 12:58
  • Thanks! The issue you mentioned says that it should be fixed in 2.7 but the error is still here. – StockBreak Jun 12 '20 at 07:28