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