Working with relationships in yii framework – reservation series part 4

 

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(
          Reservation::className(),
          ['room_id' => 'id']
          )
          ->orderBy('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">
    <tr>
        <th>Room Id</th>
        <td><?php echo $lastReservation['room_id'] ?></td>
    </tr>
    <tr>
        <th>Customer Id</th>
        <td><?php echo $lastReservation['customer_id'] ?></td>
    </tr>
    <tr>
        <th>Price per day</th>
        <td><?php echo Yii::$app->formatter->asCurrency($lastReservation['price_per_day'], 'EUR') ?></td>
    </tr>
    <tr>
        <th>Date from</th>
        <td><?php echo Yii::$app->formatter->asDate($lastReservation['date_from'], 'php:Y-m-d') ?></td>
    </tr>
    <tr>
        <th>Date to</th>
        <td><?php echo Yii::$app->formatter->asDate($lastReservation['date_to'], 'php:Y-m-d') ?></td>
    </tr>
    <tr>
        <th>Reservation date</th>
        <td><?php echo Yii::$app->formatter->asDate($lastReservation['reservation_date'], 'php:Y-m-d H:i:s') ?></td>
    </tr>
</table>

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.

About the author

Deven Rathore

I'm Deven Rathore, a multidisciplinary & self-taught designer with 3 years of experience. I'm passionate about technology, music, coffee, traveling and everything visually stimulating. Constantly learning and experiencing new things.

  • Hello and thank you for this great YII2 reservation series.
    I’ve read this part 4 of reservation project & its wonderful. Also it’s really more practical to work out with the code while reading and I was unable to find the project code files of this series on the website.
    Is there any way to get the codes? It’s really taking long to read & implement all the series codes just to have a single part working!

Pin It on Pinterest

Shares

Get the best in web dev

Join dunebook.com and recieve best in web dev , once a week FREE

An email has been Sent to your Inbox ! Please Confirm your Subscription :)