ActiveRecord provides us with skills to work with relationships between database tables. Yii2 employs two methods to establish the relationship between the current and other ActiveRecord classes: hasOne and hasMany, which return an ActiveQuery based on the multiplicity of the relationship.

The first method hasOne() returns at most one related record that matches the criteria set by this relationship, and hasMany() returns multiple related records that match the criteria set by this relationship.

Both methods require that the first parameter is the class name of the related ActiveRecord and that the second parameter is the pair of primary keys that are involved in the relationship: the first key is relative to a foreign ActiveRecord and the second key is related to the current ActiveRecord.

Usually, hasOne() and hasMany() are accessed from properties that identify which object (or objects) will be returned.

The method in this example is:

class Room extends ActiveRecord
    public function getReservations()
return $this->hasMany(Reservation::className(), ['room_id' => 'id']);

By calling $room->reservations, framework will execute this query:

SELECT * FROM `reservation` WHERE `room_id` = id_of_room_model

The use of the hasOne() method is similar, and as an example will look like this:

class Reservation extends ActiveRecord
    public function getRoom()
return $this->hasOne(Room::className(), ['id' => 'room_id']);

Calling $reservation->room, framework will execute this query:

SELECT * FROM `room` WHERE `id` = reservation_id

Remember that when we call a property that contains the hasOne() or hasMany() methods, a SQL query will be executed and its response will be cached. So, the next time that we call the property, a SQL query will not be executed and the last cached response will be released.

This approach to get related data is called lazy loading, which means that data is loaded only when it is effectively requested.

Now let’s write an example to display the last reservation details about a room. Create a reservations model class using Gii if you have not done so before.

First of all, we need some data to work with. Insert this record in the customer table:

INSERT INTO `customer` (`id` ,`name` ,`surname` ,`phone_number`) VALUES ( NULL , 'James', 'Foo', '+39-12345678');

In the reservation table, insert these records:

INSERT INTO `reservation` (`id`, `room_id`, `customer_id`, `price_per_day`, `date_from`, `date_to`, `reservation_date`) VALUES (NULL, '2', '1', '90', '2015-04-01', '2015-05-06', NULL), (NULL, '2', '1', '48', '2019-08-27', '2019-08-31', CURRENT_TIMESTAMP);

Open the room model in basic/models/Room.php and append this property declaration at the bottom of the file:

    public function getLastReservation()
        return $this->hasOne(
          ['room_id' => 'id']

As said before, hasOne() and hasMany() return an ActiveQuery instance. We can append any methods to complete the relationship as we have done before by appending the orderBy() method to get the first record.

Create a new action named actionLastReservationByRoomId($room_id) in the Rooms controller, with the following content:

    public function actionLastReservationByRoomId($room_id)
        $room = Room::findOne($room_id);
        // equivalent to
        // SELECT * FROM reservation WHERE room_id = $room_id
        $lastReservation = $room->lastReservation;
        // next times that we will call $room->reservation, no sql query will be executed.
        return $this->render('lastReservationByRoomId', ['room' => $room, 'lastReservation' => $lastReservation]);
    Finally, create the view in basic/views/rooms/lastReservationByRoomId.php with this content:<table class="table">
        <th>Room Id</th>
        <td><?php echo $lastReservation['room_id'] ?></td>
        <th>Customer Id</th>
        <td><?php echo $lastReservation['customer_id'] ?></td>
        <th>Price per day</th>
        <td><?php echo Yii::$app->formatter->asCurrency($lastReservation['price_per_day'], 'EUR') ?></td>
        <th>Date from</th>
        <td><?php echo Yii::$app->formatter->asDate($lastReservation['date_from'], 'php:Y-m-d') ?></td>
        <th>Date to</th>
        <td><?php echo Yii::$app->formatter->asDate($lastReservation['date_to'], 'php:Y-m-d') ?></td>
        <th>Reservation date</th>
        <td><?php echo Yii::$app->formatter->asDate($lastReservation['reservation_date'], 'php:Y-m-d H:i:s') ?></td>

Point your browser to http://hostname/basic/web/rooms/last-reservation-by-room-id?room_id=2 to visualize this frame:

Working with relationships

Only the last reservation inserted in the database will be displayed.