I have this Warehouse → many to many → Product → has many → Sale

My relationships

warehouse.php

public function products () {
    return $this->belongsToMany(Product::class)->withPivot('quantity');
}

product.php

public function sales () {
    return $this->hasMany(Sale::class);
}

I want to get sales for each warehouse

What I have tried

return Warehouse::with('products.sales')->get();

Unexpected restult

{
    "id": 1,
    "warehouse": "India",
    "products": [
        {
            "id": 19,
            "name": "Taylor Lester",
            "price": "745.00",
            "sales": [
                {
                    "id": 1,
                    "warehouse_id": 1,
                    "product_id": 19,
                    "user_id": 1,
                    "quantity": 20
                },
                {
                    "id": 2,
                    "warehouse_id": 1,
                    "product_id": 19,
                    "user_id": 1,
                    "quantity": 30
                }
            ]
        }
    ]
},
{
    "id": 2,
    "warehouse": "Malaysia",
    "products": [
        {
            "id": 19,
            "name": "Taylor Lester",
            "price": "745.00",
            "sales": [
                {
                    "id": 1,
                    "warehouse_id": 1,
                    "product_id": 19,
                    "user_id": 1,
                    "quantity": 20
                },
                {
                    "id": 2,
                    "warehouse_id": 1,
                    "product_id": 19,
                    "user_id": 1,
                    "quantity": 30
                }
            ]
        }
    ]
}

What I want is for each warehouse get the sales. Like Indian warehouse has only 2 sales but Malaysian warehouse has only 1 sale

How can I implement this approach?

CodePudding user response：

use withCount in laravel to only count related models

return Warehouse::with('products' => function($query){
    $query->withCount('sales');
})->get();

CodePudding user response：

According to the data, your Sale model has a warehouse_id column. Why is that relationship not on your Warehouse model already?

class Warehouse extends Model
{
    public function sales () {
        return $this->hasMany(Sale::class);
    }
}

And then

return Warehouse::with('sales')->get();