In Laravel 9, while preloading is added, constraints are added for preloads at the same time
1. The existing implementation is as follows, is a nested preload, sql is as follows
return $builder->distinct()->with('items.orderItem')->get();
2. Now you need to add constraints for items, and the final implementation is as follows
// return $builder->distinct()->with('items.orderItem')->get();
return $builder->distinct()->with(['items' => function ($query) {
$query->whereRaw('(signed_quantity + signed_damaged_quantity) < return_quantity');
}, 'items.orderItem'])
->get();
3. The generated SQL is as follows, which is in line with expectations. as shown in Figure 1
Figure 1
select
*
from
`items`
where
`items`.`return_order_id` in (230)
and (signed_quantity + signed_damaged_quantity) < return_quantity
and `items`.`deleted_at_gmt` is null
