Yii2 join a subquery

Question

I'm building a booking site, where properties (ex: hotel) have rooms and you can book them.

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 find() as a relation

Do you guys have any suggestion or opinion about this?

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 > -1 OR booking_status.status IS NULL)
                 AND booking.check_in < '2017-10-18'
                 AND booking.check_out > '2017-10-14'
         )
         GROUP BY property_id
       ) max_people
    ON max_people.id = property_id

ORDER BY property.id

Yes, I taught about that, but the problem is with views that I can't add parameters to them, and in the above query there is the 2 parameters: check in (2017-10-14) and check out (2017-10-18). And you can't pass parameters to db views — Szántó Zoltán, Sep 29 '17 at 13:28
In this case, you could use a function instead the view (for example: https://stackoverflow.com/questions/23421771/how-to-return-table-from-mysql-function) — Fabrizio Caldarelli, Sep 29 '17 at 13:29
Yes, but thats writing spaghetti code and complicating things. I want to use it as a relation if it's possible — Szántó Zoltán, Sep 29 '17 at 13:35

score 2 · Accepted Answer · answered Oct 10 '17 at 18:05

The simplest solution is use the createCommand with params:

\Yii::$app->db->createCommand("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 > -1 OR booking_status.status IS NULL)
             AND booking.check_in < ':startdate'
             AND booking.check_out > ':finishdate'
     )
     GROUP BY property_id
   ) max_people
ON max_people.id = property_id

ORDER BY property.id")
             ->bindValues([':startdate' => $startDate, ':finishdate' => $startDate])
             ->execute();

Other way is to split query for subqueries and try to use AR. It will be something like that:

$subSubQuery=Booking::find()
->select(['room_id'])
->join('booking_status', 'booking.last_status_id = booking_status.id')
->join('booking_room', 'booking.id = booking_status.id')
->join('property', 'booking.property_id= booking_propery.id')
->andWhere(['or',
        ['>','booking_status.status',-1],
        [ 'booking_status.status'=>null],
])
->andWhere(['<','booking.check_in',$startDate])
->andWhere(['>','booking.checkout',$finishDate])
->groupBy('property_id');

$subQuery = Room::find()->select(['property_id as id','sum(max_people) as total'])->andFilterWhere(['not in', 'answers_metering.id', $subSubQuery]);

$query = Property::find()->select(['property.id','property_lang.name','max_people.total'])
->join($subQuery. ' as max_people', 'max_people.id = property.id')
->join('property_lang', 'property.id = property_lang.property_id AND property_lang.lang_id = \'hu\'')
->orderBy(['property.id' => SORT_ASC])
->all();

AR seems not to be working. Have you tested it? I would love to implement it, but I'm getting error: **Object of class ... could not be converted to string** — user2511599, Apr 10 '18 at 14:25

Yii2 join a subquery

1 Answers1