title: Yii2 join a subquery
body: <p>I'm building a booking site, where properties (ex: hotel) have rooms and you can book them.</p>
  
  <p>I've made a raw SQL query which filters the available rooms in a specified date range, but I don't know how to implement it in a YII AR way: using with active record <code>find()</code> as a relation</p>
  
  <p>Do you guys have any suggestion or opinion about this? </p>
  
  <pre><code>SELECT
    property.id,
    property_lang.name,
    max_people.total
  FROM property
    JOIN property_lang ON (property.id = property_lang.property_id AND property_lang.lang_id = 'hu')
    JOIN (
           SELECT
             property_id     AS id,
             sum(max_people) AS total
           FROM room
           WHERE room.id NOT IN (
             SELECT room_id
             FROM booking
               JOIN booking_status ON (booking.last_status_id = booking_status.id)
               JOIN booking_room ON (booking.id = booking_room.id)
               JOIN property ON booking.property_id = property.id
             WHERE (booking_status.status &gt; -1 OR booking_status.status IS NULL)
                   AND booking.check_in &lt; '2017-10-18'
                   AND booking.check_out &gt; '2017-10-14'
           )
           GROUP BY property_id
         ) max_people
      ON max_people.id = property_id
  
  ORDER BY property.id
  </code></pre>