Yii2 How to execute query with subquery using Active Records?

Question

Yii

How to fulfill such a query using Active Record models?

SELECT rooms.id_room, rooms.number
    FROM rooms
    LEFT JOIN reservation ON rooms.id_room = reservation.id_room
    WHERE rooms.id_room NOT IN (
    		SELECT reservation.id_room
    		FROM reservation
    		WHERE CURDATE() BETWEEN reservation.lock_start AND reservation.lock_finish
    )

I do this but an error appears Undefined variable: rdRooms

public function getReservation()
    {
    	return $this->hasMany(Reservation::className(), ['id_room' => 'id_room']);
    }
............................................................................................................................
$rdRooms = (new Query)->select('reservation.id_room')
    				->from('reservation')
    				->where(':now BETWEEN reservation.lock_start AND reservation.lock_finish')
            	->addParams([':now' => date('Y-m-d')]);
    	
$rooms = Rooms::find()->joinWith([
        'reservation' => function ($query) {
            $query->where(['rooms.id_room' => $rdRooms]);
        }

Answer 1

in order for the variable to be visible, you need to pass it to an anonymous function, it seems like this:

$rooms = Rooms::find()->joinWith([
        'reservation' => function ($query) use ($rdRooms){
            $query->where(['rooms.id_room' => $rdRooms]);
        }