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 fi rst 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 fi rst 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 fi rst 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 fi le:
public function getLastReservation() {
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 fi rst 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
>asCurrency($lastReservation['price_per_day'], 'EUR') ?></td>
</tr>
<tr>
<th>Date from</th>
<td><?php echo
>asDate($lastReservation['date_from'], 'php:Y-m-d') ?></td>
</tr>
<tr>
<th>Date to</th>
<td><?php echo
>asDate($lastReservation['date_to'], 'php:Y-m-d') ?></td>
</tr>
<tr>
<th>Reservation date</th>
<td><?php echo
>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:
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 effi cient 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:
<?php $lastReservation = $room->lastReservation; ?>
<tr>
<td><?php echo $lastReservation['room_id'] ?></td>
<td><?php echo $lastReservation['customer_id'] ?></td>
<td><?php echo
>asCurrency($lastReservation['price_per_day'], 'EUR') ?></td>
<td><?php echo
>asDate($lastReservation['date_from'], 'php:Y-m-d') ?></td>
<td><?php echo
>asDate($lastReservation['date_to'], 'php:Y-m-d') ?></td>
<td><?php echo
>asDate($lastReservation['reservation_date'], 'php:Y-m-d H:i:s') ?></td>
</tr>
<?php } ?>
In this view, the last reservation data will be displayed for each room. Since the fi rst room has no reservations, an empty row will be displayed. This is the result:
Last reservation for every room
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 fi nd 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 fi nd 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 fi rst 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