Hello,
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 listing_rooms, listing_available and listing_booked to find out which room type was booked more than available between selected date.
-
join listing_rooms with listing_blocked 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?
Thank you,