[FIXED] MySQL - Doctrine: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains

Issue

I'm trying writing this MySQL query

http://sqlfiddle.com/#!9/e417f3/2

SELECT user.name as user, offer.score, course.name as course, city.name as city
FROM offer
INNER JOIN course ON course.id = offer.course_id
INNER JOIN user ON user.id = offer.user_id
INNER JOIN city ON city.id = user.city_id
GROUP BY offer.course_id
ORDER BY offer.score DESC;

in Doctrine

return $this
  ->createQueryBuilder('offer')
  ->select('offer.id')
  ->innerJoin('offer.course', 'course')
  ->innerJoin('offer.tutor', 'user')
  ->innerJoin('user.city', 'city')
  ->groupBy('offer.course')
  ->orderBy('offer.score', 'DESC')
  ->getQuery();

And return this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Can help me to solve it?

Solution

Please read more about in this manual : https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

The problem is that your SELECT columns need to be agregated when using GROUP BY clause. So in the SELECT query you should use functions like MAX() SUM() ANY_VALUE() - which could do your query working even with ONLY_GROUP_BY enabled (see mysql manual) for examples.

Answered By - user3760296