Lazy and Eager Loading
As described earlier, when you access the related objects for the first time, ActiveRecord will perform a DB query to retrieve the corresponding data and populate it into the related objects. No query will be performed if you access the same related objects again. We call this lazy loading. For example,
// SQL executed: SELECT * FROM customer WHERE id=1
$customer = Customer::findOne(1);
// SQL executed: SELECT * FROM order WHERE customer_id=1
$orders = $customer->orders;
// no SQL executed
$orders2 = $customer->orders;
Lazy loading is very convenient to use. However, it may suffer from a performance issue in the following scenario:
// SQL executed: SELECT * FROM customer LIMIT 100
$customers = Customer::find()->limit(100)->all();
foreach ($customers as $customer) {
// SQL executed: SELECT * FROM order WHERE customer_id=...
$orders = $customer->orders;
// ...handle $orders...
}
How many SQL queries will be performed in the above code, assuming there are more than 100 customers in the database? 101! The first SQL query brings back 100 customers. Then for each customer, a SQL query is performed to bring back the orders of that customer.
To solve the above performance problem, you can use the so-called eager loading approach by calling yii\db\ActiveQuery::with():
// SQL executed: SELECT * FROM customer LIMIT 100;
// SELECT * FROM orders WHERE customer_id IN (1,2,...)
$customers = Customer::find()->limit(100)
->with('orders')->all();
foreach ($customers as $customer) {
// no SQL executed
$orders = $customer->orders;
// ...handle $orders...
}
As you can see, only two SQL queries are needed for the same task!
Info: In general, if you are eager loading
N
relations among whichM
relations are defined withvia()
orviaTable()
, a total number of1+M+N
SQL queries will be performed: one query to bring back the rows for the primary table, one for each of theM
junction tables corresponding to thevia()
orviaTable()
calls, and one for each of theN
related tables.
Note: When you are customizing
select()
with eager loading, make sure you include the columns that link the related models. Otherwise, the related models will not be loaded. For example,
$orders = Order::find()->select(['id', 'amount'])->with('customer')->all();
// $orders[0]->customer is always null. To fix the problem, you should do the following:
$orders = Order::find()->select(['id', 'amount', 'customer_id'])->with('customer')->all();
Sometimes, you may want to customize the relational queries on the fly. This can be done for both lazy loading and eager loading. For example,
$customer = Customer::findOne(1);
// lazy loading: SELECT * FROM order WHERE customer_id=1 AND subtotal>100
$orders = $customer->getOrders()->where('subtotal>100')->all();
// eager loading: SELECT * FROM customer LIMIT 100
// SELECT * FROM order WHERE customer_id IN (1,2,...) AND subtotal>100
$customers = Customer::find()->limit(100)->with([
'orders' => function($query) {
$query->andWhere('subtotal>100');
},
])->all();