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:
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:
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:
The second method, viaTable()
, is based on direct access to a physical table in the database and supports three parameters:
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:
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
)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]); }
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:
3.135.183.89