How to get first item of collection into DQL

Question

In my DB I have two tables Booking & Week. A Booking have one-to-many relationship with Week.

Into Week I have a "start" and a "end" both Datetime

I would like to select all bookings (edit) that start in less than 45 days but I can't find how to achive this.

Here's what I wrote:

$bookings = $doctrine->getRepository('MyBundle:Booking')
   ->createQueryBuilder('b')
   ->where('DATE_DIFF(CURRENT_DATE(), b.weeks) <= 45')
   ->getQuery()->getResult();

Of course this is not working because b.weeks is collection. Instead of "weeks" I need to get the first week (aka week with the lowest "start").

Can you help me to do this?

PS: I can't do a findAll and then filter because there is more than 20.000 bookings into database....

Answer 1

you can join the week entity when do the where on the start datetime

$booking = $doctrine->getRepository('MyBundle:Booking')
   ->createQueryBuilder('b')
   ->join('b.weeks', 'w')
   ->where('w.start <= :start')
   ->setParameter(':start', new \DateTime('+45 days'))
   ->orderBy('w.start', 'ASC')
   ->getQuery()->getResult();

that will give you the first result of a sorted query... I haven't run this to make sure it works... but it'll give you an idea.. this solves the problem of the "weeks" being a collection .. with this method you can get to the start of the week and devise a logic that'll match your application.