Laravel eloquent sum column is not appearing in results

Question

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.

Answer 1

I fixed my issue creating a hasManyThrough relation inside my Supplier model:

public function detail_purchases(){
        return $this->hasManyThrough(PurchaseDetail::class, Purchase::class);
    }

Then using Eloquent I can use this relation:

   $collectionCenters = Supplier::withCount(['detail_purchases as purchaseQtys' => function($query){
                             return $query->select(\DB::raw('SUM(qty)'))->where('unit', '=', $this->unit);
                         }])->where('supplier_type','=','persona_juridica')
                         ->orderBy('purchaseQtys','desc')
                         ->get();

Output:

#attributes: array:9 [▼
        "id" => 4
        "supplier_type" => "persona_juridica"
        "department_id" => 14
        "province_id" => 1403
        "district_id" => 140308
        "community_id" => null
        "created_at" => "2021-11-04 09:55:49"
        "updated_at" => "2021-11-04 09:55:49"
        "purchaseQtys" => "6"

I also created magic methods or attributes:

   public function getQtySoldKgAttribute(){
        return  $this->detail_purchases()->where('unit','kg')->sum('qty');
   
   }
   public function getQtySoldTonsAttribute(){
        return $this->detail_purchases()->where('unit','t')->sum('qty');
  }

  public function getQtySoldGramsAttribute(){
    return $this->detail_purchases()->where('unit','g')->sum('qty');
 }
 public function getQtySoldGallonsAttribute(){
    return $this->detail_purchases()->where('unit','gal')->sum('qty');
 }
 public function getQtySoldLitersAttribute(){
    return $this->detail_purchases()->where('unit','l')->sum('qty');
 }
 public function getQtySoldMillilitersAttribute(){
    return $this->detail_purchases()->where('unit','ml')->sum('qty');
 }

When you use a sum firt we need to add where condition and then the sum directive, if you add first the sum and then the where it will not works.

Cheers!.

Answer 2

whereHas doesn't change the result set (it is just restricting the result). https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence

You may use something like this in laravel 8

withSum: https://laravel.com/docs/8.x/eloquent-relationships#other-aggregate-functions

$collectionCenters = Supplier::query()
    ->whereHas('purchases', function ($q1) {
        $q1->whereHas('details', function ($q2) {
            $q2->where('unit', 'kg');
        });
    })
    ->with([
        'purchases' => function ($query) {
            return $query->withSum('details', 'qty')->where('unit', 'kg');
        }
    ])
    ->where('supplier_type', '=', 'juridic_person')
    ->get();