I have some problem about phql and subquery. I have a room booking system with 4 tables as follow
listing_rooms -----> room data
listing_available -----> total room available on any date
listing_booked ----> room booking on any date
listing_blocked ----> room that admin block on any date
I try to find room type that available for booking by
join listingrooms, listingavailable and listing_booked to find out which room type was booked more than available between selected date.
join listingrooms with listingblocked to find out which room type not blocked on selected date.
result from 2 that not in 1 is room that user can book.
I use sql query as below in mysql and ger a result
select * from listing_rooms tlr left join listing_blocked tlb on tlr.id = tlb.room_id and date between '2015-07-06' and '2015-07-11' where tlb.room_id is NULL and tlr.id not in ( select sq1.id from ( <-- error here select tlr.id, count(*) as booked, (tla.available - count(*)) as room_left1, (tlr.total_room - count(*)) as room_left2 from listing_rooms as tlr left join listing_booked as tlb on tlr.id = tlb.room_id and tlb.date between '2015-07-06' and '2015-07-11' left join listing_availability as tla on tlr.id = tla.room_id and tla.date = tlb.date group by tlb.date, tlb.room_id having room_left1 <= 0 or room_left2 <= 0 ) as sq1 )
First I try to use query builder but not success so I try to use PHQL but still get syntax error at ( select tlr.id, count(*) as booked. How do I run this query with PHQL? or have othermethod for this query?