Skip to content
Advertisement

Symfony doctrine orderby and group by (distinct)

I am trying to get the latest booked courses (unique). I have tried the following with the Doctrine querybuilder:

Without the orderby it works but it will traverse the bookings ascending. I need the latest booked courses. With this query I am getting this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column … which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 500 Internal Server Error – DriverException

I also tried the following:

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column … which is not in SELECT list; this is incompatible with DISTINCT

Now I have searched for solutions and basically found a lot of suggestions to disable ONLY_FULL_GROUP_BY. Disable ONLY_FULL_GROUP_BY

Some comments state there that it is not wise to do that in order to comply to sql standards. Then how is this possible in a way that does work?

Advertisement

Answer

You could try this way:

You’ll get only the bookings with greatest id for every course, so no need for grouping by course.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement