Constraining Eager Loads

A closure as the array value constrains an eager load, so where(), latest() and even limit() apply to the children. Dot notation nests. chunkById() (Chunking and Cursors) runs its eager loads once per chunk, keeping memory bounded without bringing N+1 back:

use App\Models\Order;
use App\Models\Product;
$sum = fn ($orders) => $orders->each(fn ($order) => $order->products->sum('pivot.quantity'));
measure('chunk with', fn () => Order::with('products:id')->chunkById(500, $sum));
DB::listen(fn ($q) => print('-- '.wordwrap($q->toRawSql(), 88, "\n   ").PHP_EOL));
Product::with([
    'category:id,name',
    'reviews' => fn ($query) => $query->where('rating', 5)->latest()->limit(2),
    'reviews.customer:id,name',
])->whereKey([1, 4])->get();

chunk with      9 queries    31.6 ms in MySQL   277.8 ms in all
-- select * from `products` where `products`.`id` in (1, 4)
-- select `id`, `name` from `categories` where `categories`.`id` in (2)
-- select * from (select *, row_number() over (partition by `reviews`.`product_id` order by
   `created_at` desc) as `laravel_row` from `reviews` where `reviews`.`product_id` in (1,
   4) and `rating` = 5) as `laravel_table` where `laravel_row` <= 2 order by `laravel_row`
-- select `id`, `name` from `customers` where `customers`.`id` in (1, 3, 18, 401)

Five chunk queries plus one eager load per chunk make 9; without with() the same loop sent 2,005. A plain LIMIT 2 would return two reviews in all, so Eloquent numbers each product's reviews with ROW_NUMBER() OVER (PARTITION BY ...) (Aggregates and Windows) and keeps the first two; before MySQL 8.0.11 524 it emulates this with user variables. 'reviews' => ['customer'] is the array form of the nesting.