Laravel hasManyDeep issue-CodePudding

I have implemented this relationship where a warehouse belongsToMany products that has Many sales.

My models be like

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 access sales directly from my Warehouse model to sum a column in the sales table.

I have used staudenmeir's GitHub package and added a sales method in my Warehouse model.

public function sales () {
    return $this->hasManyDeep(Sale::class, ['product_warehouse', Product::class]);
}

What I want to do is basically to sum total column of sales table so I've added a withSum() method in my WarehouseController like this

return Warehouse::query()
        ->withSum('sales', 'total')
        ->get();

Result

[
   {
      "id": 1,
      "warehouse": "Algeria",
      "sales_sum_total": "1000"
   },
   {
      "id": 2,
      "warehouse": "India",
      "sales_sum_total": "1000"
   }
]

The issue here is when I add a new sale to the India warehouse it returns the same values for all warehouses. I think that I'm not using the hasManyDeep() method the right way or perhaps it doesn't work for my use case. Is there anything I can do to get this to work?

Edit: My database structure

    Schema::create('warehouses', function (Blueprint $table) {
        $table->id();
        $table->string('warehouse');
    });

    Schema::create('products', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->decimal('price');
    });

    Schema::create('product_warehouse', function (Blueprint $table) {
        $table->id();
        $table->foreignId('product_id')->constrained()->cascadeOnDelete();
        $table->foreignId('warehouse_id')->constrained()->cascadeOnDelete();
        $table->integer('quantity')->default(0);
    });

    Schema::create('sales', function (Blueprint $table) {
        $table->id();
        $table->foreignId('warehouse_id')->constrained()->cascadeOnDelete();
        $table->foreignId('product_id')->constrained()->cascadeOnDelete();
        $table->integer('quantity');
        $table->decimal('total');
    });

CodePudding user response：

While you have warehouse_id in sales table you can deal with it as a simple hasMany relation and call the sum on it, I tested your case and got the right results, I just added sales relation on warehouse model:

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

And I just did:

return Warehouse::withSum('sales', 'total')->get();