1. The existing implementation is as follows; it is a nested eager load, and the SQL is as follows:
PHP
return $builder->distinct()->with('items.orderItem')->get();
2. Now we need to add a constraint to items. The final implementation is as follows:
PHP
// 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 meets expectations. See Figure 1.
SQL
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