Question 63956 in PhpMyAdmin Question posted in
The official documentation can be found here.

Laravel eloquent sum column is not appearing in results – Phpmyadmin

I have the next database:

enter image description here

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:

enter image description here

Phpmyadmin output from query:

enter image description here

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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!.


  2. 0

    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();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search