Subquery in phql syntax error

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

  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 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,

Subqueries in the form clause aren't supported. You can use raw queries: https://docs.phalconphp.com/en/latest/reference/phql.html#using-raw-sql