John Davidson

Message:

I have the next database:

Therefore I have three php models Purchase, Supplier and PurchaseDetail.
My model Supplier has a relation hasMany with Purchase:

public function purchases() {

        return $this->hasMany(Purchase::class, 'supplier_id');

    }

And the model Purchase has a relation hasMany with PurchaseDetail :

public function details(){

        return $this->hasMany(PurchaseDetail::class, 'purchase_id');

    }

From here there is not problems with the relations. The problem comes for me when I trying of get the qty total for all purchases of each supplier. I did the next query:

$collectionCenters = Supplier::whereHas('purchases', function($q1){

                    $q1->whereHas('details', function($q2){

                        $q2->select(\DB::raw('SUM(qty) as purchasesQtyKg'))

                        ->where('unit', 'kg');

                    });

            })->where('supplier_type','=','juridic_person')

            ->get();

Output query from laravel debug bar:

select * from `suppliers` where exists (select * from `purchases` where `suppliers`.`id` = `purchases`.`supplier_id` and exists (select SUM(qty) as purchasQtyKg from `purchase_details` where `purchases`.`id` = `purchase_details`.`purchase_id` and `unit` = 'kg') and `purchases`.`deleted_at` is null) and `supplier_type` = 'juridic_person'

As you can see, I'm using the relations with whereHas directive. However nothing happens, it oly throws the three suppliers of my database but the column with alias purchasesQtyKg it not appears in the results:

Phpmyadmin output from query:

I also did something like this:

 $collectionCenters = Supplier::where('supplier_type','=','juridic_person')

                            ->with(['purchases.details' => function($query){

                                $query->select(\DB::raw("SUM(purchase_details.qty) as purchaseQtyKg"))

                                ->where('unit', $this->unit);

                            }])->get();

But nothing happens, I'm afraid because this should work. I will appreciate any idea guys for fix this problem. Thanks you so much.

---

My Answer:
It looks like you are trying to retrieve the sum of the 'qty' column from the 'purchase_details' table for each supplier. One way to achieve this is by using the `withSum` method provided by Laravel Eloquent.

You can modify your query like this:

php
$collectionCenters = Supplier::where('supplier_type','=','juridic_person')
->withSum('purchases.details:qty', function ($query) {
$query->where('unit', 'kg');
})
->get();


This will retrieve the sum of the 'qty' column from the 'purchase_details' table where the 'unit' is 'kg' for each supplier. Make sure to adjust the column names and relationships according to your actual database structure.

Also, make sure to check the Laravel documentation for more information on the `withSum` method: https://laravel.com/docs/8.x/eloquent-relationships#eager-loading-specific-columns