Working with relationships

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

A visualization of the last reservation of a room with id = 2

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

What about displaying all the last reservations for each room in a single table?

Here, the lazy loading approach will have performance issues because for every room, it will execute a single SQL query to get data for the last reservation. This is a code snippet in the view:

for($roomsList as $room)
{
    // SELECT * FROM reservation WHERE room_id = $room->id
      $lastReservation = $room->lastReservation;
}

In order to complete the script's execution, it will execute as many related SQL queries as the number of rooms, and when the number of rooms grows, this solution will not be efficient anymore.

The Yii2 framework provides another type of loading data, named eager loading, to solve this kind of problem.

Eager loading is applied using the with() method of ActiveQuery. This method's parameters can be either one or multiple strings, or a single array of relation names and the optional callbacks to customize the relationships.

When we get a rooms list, if we apply the with() method to the query, a second SQL query will automatically be executed and this will return the list of the last reservations for each room.

With this example, we will get a rooms list and a list of the lastReservation relation for each room entry. In this way, when we refer to $room->lastReservation, no other SQL query will be executed:

// SELECT * FROM `room`
// SELECT * FROM `reservation` WHERE `room_id` IN ( room_id list from previous select ) ORDER BY `id` DESC
$rooms = Room::find()
->with('lastReservation')
->all();

// no query will be executed
$lastReservation = $rooms[0]->lastReservation;

Let's write a complete example to get a full list of the last reservations for each room. In basic/controllers/RoomsController.php, append a new action named actionLastReservationForEveryRoom():

    public function actionLastReservationForEveryRoom()
    {
            $rooms = Room::find()
            ->with('lastReservation')
            ->all();
        
            return $this->render('lastReservationForEveryRoom', ['rooms' => $rooms]);
    }

This action will pass a list of rooms named lastReservationForEveryRoom to the view, together with the lastReservation relation loaded using the eager loading.

Create a view named lastReservationForEveryRoom.php in basic/views/rooms/lastReservationForEveryRoom.php:

<table class="table">
    <tr>
        <th>Room Id</th>
        <th>Customer Id</th>
        <th>Price per day</th>
        <th>Date from</th>
        <th>Date to</th>
        <th>Reservation date</th>
    </tr>
    <?php foreach($rooms as $room) { ?>
    <?php $lastReservation = $room->lastReservation; ?>
    <tr>
        <td><?php echo $lastReservation['room_id'] ?></td>
        <td><?php echo $lastReservation['customer_id'] ?></td>
        <td><?php echo Yii::$app->formatter->asCurrency($lastReservation['price_per_day'], 'EUR') ?></td>
        <td><?php echo Yii::$app->formatter->asDate($lastReservation['date_from'], 'php:Y-m-d') ?></td>
        <td><?php echo Yii::$app->formatter->asDate($lastReservation['date_to'], 'php:Y-m-d') ?></td>
        <td><?php echo Yii::$app->formatter->asDate($lastReservation['reservation_date'], 'php:Y-m-d H:i:s') ?></td>
    </tr>
    <?php } ?>
</table>

In this view, the last reservation data will be displayed for each room. Since the first room has no reservations, an empty row will be displayed. This is the result:

Working with relationships

Last reservation for every room

Note

There are two variants to the with() method: joinWith() and innerJoinWith(), which apply a left join or an inner join to a primary query.

For example, this is the use of joinWith() with:

            $rooms = Room::find()
            ->leftJoinWith('lastReservation')
            ->all();

The preceding code snippet is equivalent to:

SELECT `room`.* FROM `room` LEFT JOIN `reservation` ON `room`.`id` = `reservation`.`room_id` ORDER BY `id` DESC

SELECT * FROM `reservation` WHERE `room_id` IN ( room_id list from previous sql respone ) ORDER BY `id` DESC

Remember that the inner join selects all rows from both tables as long as there is a match between the columns in both tables; instead, the left join returns all rows from the left table (room), with the matching rows in the right table (reservation). The result is NULL in the right side when there is no match.

Sometimes it happens that we need more than one level of relationship between tables. For example, we could find a customer related to a room. In this case, starting from the room, we pass through the reservation and go from the reservation to the customer.

The relationship here will be:

room -> reservation -> customer

If we want to find out the customer object from the room object, just type:

$customer = $room->customer;

Generally, we have more levels of relationship, but in this case only two (reservation and customer).

Yii2 allows us to specify a junction table using the via() or viaTable() method. The first one, via(), is based on an existing relationship in the model, and it supports two parameters:

  • Relation name
  • A PHP callback parameter to customize the associated relation

The second method, viaTable(), is based on direct access to a physical table in the database and supports three parameters:

  • The first parameter is a relation or table name
  • The second parameter is the link associated with the primary model
  • The third parameter is a PHP callback to customize the associated relation

Example – using a relationship to connect rooms, reservations, and customers

In this example, we will look at how to build a single view that displays the rooms, reservations, and customers lists at the same time; when a user clicks on the Detail button of rooms record, the reservations list will be filtered with data linked to that room. In the same way, when a user clicks on the Detail button of a reservations record, the customers list will be filtered with data linked to that reservation.

If no parameter is passed (a condition that occurs when a page is called for the first time), either the rooms, reservations, or customers list contains a full record of data from the respective tables.

Start writing actionIndexWithRelationships in basic/controllers/RoomsController.php. This is the task list for this action:

  • Check which parameter of detail has been passed (room_id identifies that the reservations list has to be filled in with the data filtered using room_id, while reservation_id identifies that the customers list has to be filled with the data filtered using reservation_id)
  • Fill in three models: roomSelected, reservationSelected, and customerSelected to display the details and fill in three arrays of models: rooms, reservations, and customers

This is the complete content of actionIndexWithRelationships:

     public function actionIndexWithRelationships()
    {
        // 1. Check what parameter of detail has been passed
        $room_id = Yii::$app->request->get('room_id', null);
        $reservation_id = Yii::$app->request->get('reservation_id', null);
        $customer_id = Yii::$app->request->get('customer_id', null);
        
        // 2. Fill three models: roomSelected, reservationSelected and customerSelected and
        //    Fill three arrays of models: rooms, reservations and customers;
        $roomSelected = null;
        $reservationSelected = null;
        $customerSelected = null;
        
        if($room_id != null)
        {
            $roomSelected = Room::findOne($room_id);
            
            $rooms = array($roomSelected);
            $reservations = $roomSelected->reservations;
            $customers = $roomSelected->customers;
        }
        else if($reservation_id != null)
        {
            $reservationSelected = Reservation::findOne($reservation_id);
            
            $rooms = array($reservationSelected->room);
            $reservations = array($reservationSelected);
            $customers = array($reservationSelected->customer);
        }
        else if($customer_id != null)
        {
            $customerSelected = Customer::findOne($customer_id);
            
            $rooms = $customerSelected->rooms;
            $reservations = $customerSelected->reservations;
            $customers = array($customerSelected);
        }
        else
        {
            $rooms = Room::find()->all();
            $reservations = Reservation::find()->all();
            $customers = Customer::find()->all();
        }
        
        return $this->render('indexWithRelationships', ['roomSelected' => $roomSelected, 'reservationSelected' => $reservationSelected, 'customerSelected' => $customerSelected, 'rooms' => $rooms, 'reservations' => $reservations, 'customers' => $customers]);
    }

Note

Remember to add the use keyword for Customer and Reservation classes at the top of the RoomsController file:

use appmodelsReservation;
use appmodelsCustomer;

The second part of the action body requires more attention, because there are filled in selected models and list models in this specific position.

Only one parameter at a time can be selected between $room_id, $reservation_id, and $customer_id. When one of these three parameters is selected, three arrays of the Room, Reservation, and Customer model will be filled in, using the relationships in the model. For this purpose, models must have all the relationships employed in the previous code.

Let's make sure that all the relationships exist in the models.

The Room model in basic/models/Room.php must have both getReservations() and getCustomers() defined, which both use the via() method to handle the second level of relationship:

    public function getReservations()
    {
            return $this->hasMany(Reservation::className(), ['room_id' => 'id']);
    }
public function getCustomers()
    {
            return $this->hasMany(Customer::className(), ['id' => 'customer_id'])->via('reservations');
    }

The Reservation model in basic/models/Reservation.php must have getCustomer() and getRoom(), both returning a single related model:

    public function getRoom()
    {
            return $this->hasOne(Room::className(), ['id' => 'room_id']);
    }

    public function getCustomer()
    {
            return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }

Finally, the Customer model in basic/models/Customer.php must have getReservations() and getRooms(), which use the via() method to handle the second level of relationship:

    public function getReservations()
    {
            return $this->hasMany(Reservation::className(), ['customer_id' => 'id']);
    }
    
    public function getRooms()
    {
            return $this->hasMany(Room::className(), ['id' => 'room_id'])->via('reservations');
    }

Now write a view file in basic/view/rooms/indexWithRelationships.php. We will split the HTML page into three parts (three tables), using the CSS provided by Bootstrap (which we will examine widely in the next few chapters).

The first table will be for the rooms list, the second table for the reservations list, and the last one for the customers list:

<?php
use yiihelpersUrl;
?>

<a class="btn btn-danger" href="<?php echo Url::to(['index-with-relationships']) ?>">Reset</a>

<br /><br />
<div class="row">
    <div class="col-md-4">
        <legend>Rooms</legend>
        <table class="table">
            <tr>
                <th>#</th>
                <th>Floor</th>
                <th>Room number</th>
                <th>Price per day</th>
            </tr>
            <?php foreach($rooms as $room) { ?>
            <tr>
                <td><a class="btn btn-primary btn-xs" href="<?php echo Url::to(['index-with-relationships', 'room_id' => $room->id]) ?>">detail</a></td>
                <td><?php echo $room['floor'] ?></td>
                <td><?php echo $room['room_number'] ?></td>
                <td><?php echo Yii::$app->formatter->asCurrency($room['price_per_day'], 'EUR') ?></td>
            </tr>
            <?php } ?>
        </table>
        
        <?php if($roomSelected != null) { ?>
            <div class="alert alert-info">
                <b>You have selected Room #<?php echo $roomSelected->id ?></b>
            </div>
        <?php } else { ?>
            <i>No room selected</i>
        <?php } ?>
    </div>
    
    <div class="col-md-4">
        <legend>Reservations</legend>
        <table class="table">
            <tr>
                <th>#</th>
                <th>Price per day</th>
                <th>Date from</th>
                <th>Date to</th>
            </tr>
            <?php foreach($reservations as $reservation) { ?>
            <tr>
                <td><a class="btn btn-primary btn-xs" href="<?php echo Url::to(['index-with-relationships', 'reservation_id' => $reservation->id]) ?>">detail</a></td>
                <td><?php echo Yii::$app->formatter->asCurrency($reservation['price_per_day'], 'EUR') ?></td>
                <td><?php echo Yii::$app->formatter->asDate($reservation['date_from'], 'php:Y-m-d') ?></td>
                <td><?php echo Yii::$app->formatter->asDate($reservation['date_to'], 'php:Y-m-d') ?></td>
            </tr>
            <?php } ?>
        </table>
        
        <?php if($reservationSelected != null) { ?>
            <div class="alert alert-info">
                <b>You have selected Reservation #<?php echo $reservationSelected->id ?></b>
            </div>
        <?php } else { ?>
            <i>No reservation selected</i>
        <?php } ?>
         
    </div>
    <div class="col-md-4">
        <legend>Customers</legend>
        <table class="table">
            <tr>
                <th>#</th>
                <th>Name</th>
                <th>Surname</th>
                <th>Phone</th>
            </tr>
            <?php foreach($customers as $customer) { ?>
            <tr>
                <td><a class="btn btn-primary btn-xs" href="<?php echo Url::to(['index-with-relationships', 'customer_id' => $customer->id]) ?>">detail</a></td>
                <td><?php echo $customer['name'] ?></td>
                <td><?php echo $customer['surname'] ?></td>
                <td><?php echo $customer['phone_number'] ?></td>
            </tr>
            <?php } ?>
        </table>
        
        <?php if($customerSelected != null) { ?>
            <div class="alert alert-info">
                <b>You have selected Customer #<?php echo $customerSelected->id ?></b>
            </div>
        <?php } else { ?>
            <i>No customer selected</i>
        <?php } ?>        
    </div>   
</div>

Test the code by pointing your browser to http://hostname/basic/rooms/index-with-relationships. This should be the result of trying to filter a room on the second floor:

Example – using a relationship to connect rooms, reservations, and customers

Rooms with relationships between reservations and customers

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.135.183.89