1

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?

Maria Brands
  • 29
  • 9
  • 1
    Remove the whole `GROUP BY offer.course_id` code line. Add DISTINCT if really needed. – Akina Jan 29 '20 at 07:49
  • 2
    I think it could be the sql_mode as explained in other response: https://stackoverflow.com/questions/36950857/sqlstate42000-syntax-error-or-access-violation-1055-expression-3-of-select – user3760296 Jan 29 '20 at 09:16
  • 1
    All the fields in SELECT must be listed in GROUP BY as well. Lately MySQL server versions have that requirement. – Đuro Mandinić Jan 29 '20 at 09:20
  • 1
    @user3760296 Hey thank you! Works for me this solution :) – Maria Brands Jan 29 '20 at 09:45

1 Answers1

2

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.

user3760296
  • 121
  • 1
  • 4