1

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

$repository = $this->getDoctrine()->getRepository('AppBundle:Booking');

$query = $repository->createQueryBuilder('b')
->select('(b.course) AS course')
->orderBy('b.id', 'DESC')
->groupBy('b.course')
->setMaxResults(5)
->getQuery();

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:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
   'SELECT DISTINCT c.id
    FROM AppBundle:Booking b
    JOIN b.course c
    ORDER BY b.id DESC'
);

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?

Community
  • 1
  • 1
DaViDa
  • 641
  • 1
  • 8
  • 28
  • Can you provide more info about your entities ? How are they mapped ? (I'm guessing ManyToOne unidirectional) – OlivierC Jan 05 '17 at 16:15
  • ManyToOne bidirectional, because of cascade remove, since bookings can't exist without a course – DaViDa Jan 06 '17 at 08:24

1 Answers1

5

You could try this way:

$query = $repository->createQueryBuilder('b')
->select('(b.course) AS course')
->leftJoin('AppBundle:Booking', 'b2', 'WITH', 'b.course = b2.course AND b.id < b2.id')
->where('b2.id IS NULL')
->orderBy('b.id', 'DESC')
->setMaxResults(5)
->getQuery();

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

sast
  • 478
  • 4
  • 8
  • Left join does the magic. For every b/b2 pair b.id < b2.id. Because we filter out all the pairs where b2 exists we get only the bookings with greatest id for every course (and only one booking for one course). – sast Jan 07 '17 at 20:50
  • Thanks for the great help! – DaViDa Jan 17 '17 at 11:25