2

My question is similar to this, but different. Can you join on a subquery with Doctrine 2 DQL?

I want to get all the rooms regardless, and left join any occupants who belong to a booking that exists on a given date.

For example (a plain mysql result set - doctrine would return objects):

Room ID   | Occupant ID | Booking ID | Booking Start | Booking End
1         | 1           | 1          | Before today  | After today
1         | 2           | 1          | Before today  | After today
2         | NULL        | NULL       | NULL          | NULL

Here's what I'm trying:

SELECT r, a, b
FROM MyBundle:Room r
LEFT JOIN r.occupants a
WITH a.booking is not null
LEFT JOIN a.booking b
WITH b.enrolmentStart <= :date
AND b.enrolmentEnd >= :date
AND b.status = 1
ORDER BY r.number ASC

Unfortunately, this gets all the rooms, with all the people who ever stayed in that room ever, but only the bookings that exist on that given date.

On the other hand, if I change to the following, I'm given only rooms that have bookings on that given date.

LEFT JOIN r.occupants a
WITH a.booking is not null
JOIN a.booking b

If I try the following, Doctrine says it didn't expect a dot after the 'a'.

LEFT JOIN r.occupants a
WITH a.booking.enrolmentStart <= :date
AND a.booking.enrolmentEnd >= :date
AND a.booking.status = 1
LEFT JOIN a.booking b

And lastly, if I try the following, doctrine is not happy about the order.

LEFT JOIN r.occupants a
WITH b.enrolmentStart <= :date
AND b.enrolmentEnd >= :date
AND b.status = 1
LEFT JOIN a.booking b

Any ideas?

Community
  • 1
  • 1
darkbluesun
  • 817
  • 8
  • 15

1 Answers1

0

I think that you can achieve this query only using left joins:

SELECT 
    r.number, o.id_occupant, b.id_booking, b.enrolmentStart, b.enrolmentEnd
FROM 
    MyBundle:Room r
    LEFT JOIN r.occupants o
    LEFT JOIN o.booking b
    AND b.status = 1
    AND b.enrolmentEnd >= :date
    AND b.enrolmentStart <= :date
ORDER BY r.number ASC

Take a look to this equivalent sql (SQL Fiddle) query.

Debugged sql:

select r.number, ref.id_occupant, ref.id_booking
from rooms r
LEFT JOIN (select * from rooms r2
left join occupants o on o.room = r2.id_room
left join booking b on b.id_booking = o.booking
where b.status = 1
and b.enrolmentEnd >= '2015/03/09'
and b.enrolmentStart <= '2015/03/10') as ref on r.id_room = ref.id_room
manix
  • 14,537
  • 11
  • 70
  • 107
  • Isn't the where statement going to eliminate the rooms that aren't booked from the results? I want all the rooms. – darkbluesun Mar 17 '15 at 21:57
  • oh! I got it. Let me make the changes – manix Mar 17 '15 at 21:59
  • mmm, can you show me an "hipotetical result" that you want to get? – manix Mar 17 '15 at 22:12
  • I have inserted an example in the top of the question for you. Thanks. – darkbluesun Mar 17 '15 at 22:30
  • Your SQL Fiddle is almost spot on despite the lack of schema in my question. The only difference is that a booking can have many occupants / an occupant belongs to one booking. – darkbluesun Mar 17 '15 at 22:33
  • this shows the schema more accurately:http://sqlfiddle.com/#!9/aee7c/2 It should not show the id of the occupant on the last line, because that booking status is zero. – darkbluesun Mar 18 '15 at 00:55
  • I feel like there's something missing from your answer between LEFT JOIN and AND... [Syntax Error] line 0, col 143: Error: Expected end of string, got 'AND' – darkbluesun Mar 18 '15 at 06:08
  • Assuming you meant WITH b.status = 1, I've got the query to run, but it returns the ids of all the occupants who have ever stayed in that room - but only the IDs of the bookings that we want. I need to eliminate those occupants who don't have the bookings we want. This almost line-for-line for the first query I state in my answer that I tried already... – darkbluesun Mar 18 '15 at 06:11
  • It is late here in my country. Morning tommorrow I will create this schema in localhost with respective doctrine models. I will let you know the query then – manix Mar 18 '15 at 06:14
  • hi @darkbluesun, I just have created the correct sql that you need. At this moment I am not sure how to parse it to DQL or Query Builder format – manix Mar 23 '15 at 17:18
  • I'd love to see the SQL out of curiosity – darkbluesun Mar 23 '15 at 23:26