symfony - DQL - JOIN WITH properties of a relation -

my question similar this, different. can join on subquery doctrine 2 dql?

i want rooms regardless, , left join occupants belong booking exists on given date.

for example (a plain mysql result set - doctrine 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 i'm trying:

select r, a, b mybundle:room r left join r.occupants a.booking not null left join a.booking b b.enrolmentstart <= :date , b.enrolmentend >= :date , b.status = 1 order r.number asc 

unfortunately, gets rooms, people ever stayed in room ever, bookings exist on given date.

on other hand, if change following, i'm given rooms have bookings on given date.

left join r.occupants a.booking not null join a.booking b 

if try following, doctrine says didn't expect dot after 'a'.

left join r.occupants a.booking.enrolmentstart <= :date , a.booking.enrolmentend >= :date , a.booking.status = 1 left join a.booking b 

and lastly, if try following, doctrine not happy order.

left join r.occupants b.enrolmentstart <= :date , b.enrolmentend >= :date , b.status = 1 left join a.booking b 

any ideas?

i think can achieve query using left joins:

select      r.number, o.id_occupant, b.id_booking, b.enrolmentstart, b.enrolmentend      mybundle:room r     left join r.occupants o     left join o.booking b     , b.status = 1     , b.enrolmentend >= :date     , b.enrolmentstart <= :date order r.number asc 

take equivalent sql ^{(sql fiddle)} query.

debugged sql:

select r.number, ref.id_occupant, ref.id_booking rooms r left join (select * rooms r2 left join occupants o on o.room = r2.id_room left join booking b on b.id_booking = o.booking b.status = 1 , b.enrolmentend >= '2015/03/09' , b.enrolmentstart <= '2015/03/10') ref on r.id_room = ref.id_room