Laravel Eloquent get nested child with filter

Question

I have these relations:

Product Combo - Product (BelongsToMany)
Product - Inventory (BelongsToMany)

I have a quantity field in the pivot table between Product and Inventory to hold the product's quantity.

I want to get all the product combos that include products and those products have to be in stock (quantity > 0).

Here is my code:

/**
 * Get available combos.
 *
 * @param $query
 * @return mixed
 */
public function scopeAvailable($query)
{
    return $query->where('status', 1)
        ->whereHas('products.inventories', function ($query) {
            $query->where('quantity', '>', 0);
        });
}

That code will work if all the products are out of stock (quantity = 0). If one of product in the combo has quantity > 0, it won't work anymore.

Thank you!

So you want to have the combos where all the products are quantity > 0? — Chin Leung

Chin Leung Chin Leung · Accepted Answer · 2020-02-11T15:28:42

You could reverse the logic from the query. Instead of retrieving the products that has a quantity greater than zero, you could retrieve the products that doesn't have a quantity equals to zero.

return $query->where('status', 1)
    ->whereDoesntHave('products.inventories', function ($query) {
        $query->where('quantity', 0);
    });

More information here: https://laravel.com/docs/6.x/eloquent-relationships#querying-relationship-absence

Laravel Eloquent get nested child with filter

1 Answers