Subquery in phql syntax error


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

  1. join listing_rooms, listing_available and listing_booked to find out which room type was booked more than available between selected date.

  2. join listing_rooms with listing_blocked to find out which room type not blocked on selected date.

  3. 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 = tlb.room_id and date between '2015-07-06' and '2015-07-11'
where tlb.room_id is NULL and not in (
    select from ( <-- error here
        select, 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 = tlb.room_id and between '2015-07-06' and '2015-07-11' 
        left join listing_availability as tla on = tla.room_id and =
        group by, 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, count(*) as booked. How do I run this query with PHQL? or have othermethod for this query?

Thank you,

Subqueries in the form clause aren't supported. You can use raw queries: