Как перевести SQL с помощью Yii2 ActiveRecord?

Question

[Кирилл Тостер]

Подскажите, плиз, как лаконично построить подобный запрос средствами Yii2 ?

SELECT
  slot.logo as `dir`, 
  room.title as `room`, 
  meeting.logo as `logo`, 
  meeting.title as `title`, 
  meeting.start as `start`, 
  meeting.end as `end`
FROM 
  slot, room, meeting
WHERE 
  slot.screen_id = :screen_id AND 
  room.id = slot.room_id AND 
  meeting.room_id = room.id AND 
  meeting.start < NOW( ) AND 
  meeting.end > NOW( )

Answer 1

[Алексей]

Если у вас есть все ActiveRecord-модели со связями, то как-то так:

$results = Slot::find()->select([
   'slot.logo as `dir`',
   'room.title as `room`',
   'meeting.logo as `logo`',
   'meeting.title as `title`',
   'meeting.start as `start`',
   'meeting.end as `end`'
])->joinWith(['meeting', 'room'])->where(
   'slot.screen_id = :screen_id AND meeting.start < NOW() AND meeting.end > NOW()',
   ['screen_id' => $screen_id]
)->all();

?

Answer 2

[Кирилл Тостер]

Спасибо за ответ, Алексей.
Запрос почему-то не сработал...
Модели связал вот таким способом. Что-то неверно?

class Slot extends \yii\db\ActiveRecord {
    public function getRoom() {
         return $this->hasOne(Room::className(), ['id' => 'room_id']);
    }
}

class Room extends \yii\db\ActiveRecord {
     public function getMeetings() {
           return $this->hasMany(Meeting::className(), ['room_id' => 'id']);
     }
}

В дебагере

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'room.title' in 'field list'
The SQL being executed was: SELECT `slot`.logo as `dir`, `room`.title as `room`, `meeting`.logo as `logo`, `meeting`.title as `title`, `meeting`.start as `start`, `meeting`.end as `end` FROM `slot` WHERE slot.screen_id = 1 AND meeting.start < NOW() AND meeting.end > NOW()

Error Info: Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'room.title' in 'field list'
)
Caused by: PDOException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'room.title' in 'field list'

Comments

[Алексей]

А что в выводе дебага за запрос получился?

[Кирилл Тостер]

Добавил сообщение из дебага

[Алексей]

Чот я затупил, мы же джойны забыли: ->joinWith(['meeting', 'room'])