0

I am trying to make this query working with the sql_mode ONLY_FULL_GROUP_BY instead of disable it as it seems not to be a good practice.

I tried to use ANY_VALUE() function but it is not recognized. I get this error :

[Syntax Error] line 0, col 13: Error: Expected known function, got 'ANY_VALUE'

Here is my query, that is only working if shut off the default sql_mode :

return $this->createQueryBuilder('m')
    ->select('m.id, m.tutor, m.allow_tester, m.title, g.title as groupTitle, gc.color, COUNT(ms.id) as length')
    ->leftJoin('m.group_modules', 'gm')
    ->leftJoin('gm.module_group', 'g')
    ->leftJoin('g.programs', 'p')
    ->leftJoin('g.color', 'gc')
    ->leftJoin('m.steps', 'ms')
    ->where('p = :p')
    ->setParameter('p', $program)
    ->groupBy('m.id')
    ->orderBy('gm.sorting', 'ASC')
    ->orderBy('g.id', 'ASC')
    ->getQuery()
    ->getResult();

This is a Symfony project working with a MySql 5.7 ovh database.

Any clue ? Thank you !

Melody
  • 74
  • 10
  • Are you sure you're using MySQL Server 5.7? It should work: https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value – Bill Karwin Aug 25 '22 at 19:28
  • I know, I read that doc before... I confirm I am working with MySql 5.7. I don't understand either. – Melody Aug 25 '22 at 19:34
  • The error thrown due `ONLY_FULL_GROUP_BY` is different. Are you positive that is the error? In any case, the fix for it is to add every field in the `SELECT` statement that is not in an aggregate function into the `GROUP BY` statement. – Jetto Martínez Aug 25 '22 at 19:58
  • 1
    @JettoMartínez This seems to be the error he got when he tried to fix the ONLY_FULL_GROUP_BY problem by using ANY_VALUE – Barmar Aug 25 '22 at 20:14
  • 1
    @Melody have you checked https://stackoverflow.com/questions/41394814/doctrine-how-to-use-any-value-mysql-function ? – Ergest Basha Aug 25 '22 at 20:57
  • @Barmar I see, I read too quickly. My bad. – Jetto Martínez Aug 25 '22 at 21:02
  • Thank you so much for your answers. I didn't have found it @Ergest. This was exactly what I needed. I installed Beberlei\doctrineextensions and added ANY_VALUE in doctrine.yaml following this usefull answer -> https://stackoverflow.com/a/57337034/5633813 It's working but don't look really nice to have all these ANY_VALUE everywhere... – Melody Aug 26 '22 at 07:20

0 Answers0