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 listingrooms, listingavailable and listing_booked to find out which room type was booked more than available between selected date.

  2. join listingrooms with listingblocked 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: