Laravel Eloquent get nested child with filter

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!

psylogic

6 years ago

Level 7

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

If you have this scope in your Product Model then I think you have to put inventories instead of products.inventories

willvincent

6 years ago

Best Answer

Level 54

Probably a good situation in which to use a subquery select...

If you're not on laravel 6, https://github.com/reinink/advanced-eloquent

Also worth watching Jonathan Reinink's Laracon US 2019 talk on eloquent performance where he covers these in detail: https://www.youtube.com/watch?v=IBUXXErAtuk

Please or to participate in this conversation.