How to use SUM() with hasMany result from Model in Laravel 5.6

Question

I Try to Get result using this Model

use Illuminate\Database\Eloquent\Model;
use DB;
class Customer_Bones extends Model
{
  protected $table = 'customer_bones';
  protected $primaryKey = 'customer_bones_id';
  protected $fillable = array(
    'created_at',
    'deleted',
    'system_user',
    'customer_id',
    'bones_id'
);
public $timestamps = false;

public function getBonusCustomers(){

    return $this->hasMany('App\Models\Bonus_Item','customer_bones_id');

}

public function getCustomer(){
    return $this->hasOne('App\Models\Customer','customer_id');
}
}

My Controller function is this

return Customer_Bones::with(array(
'getCustomer' => function($query)
{
  $query->select('customer_name','customer_id');
},
'getBonusCustomers' => function($query)
{
  $query->select(DB::raw('sum(bonus_quantity) as bonusQuantity'));
}))
->get();

I want to get sum of the bonus_quantity column that belongs to Bonus_Item Table with customer_name from customer table and some other details from customer_bones table. I had Tried above method but bonusQuantity return me null.

Can I use DB::raw('sum(bonus_quantity) as bonusQuantity') inside the select clause like above to get summation of bonus_quantity column, along with other details or is there any other method?

M Khalid Junaid · Accepted Answer · 2018-08-09T05:37:31.360

You could use withCount with raw expression in callback to get the required sum like

Customer_Bones::with(['getCustomer' => function($query){
                                          $query->select('customer_name','customer_id');
                                       }
])->withCount(['getBonusCustomers as bonusQuantity' => function($query) {
                                                            $query->select(DB::raw('SUM(bonus_quantity)'));
                                                        }
])->get();

Or you could define a new mapping in your model which returns sum per customer_bones_id

public function getBonusCustomersSum(){

    return $this->hasOne('App\Models\Bonus_Item','customer_bones_id')
                ->select('customer_bones_id',DB::raw('sum(bonus_quantity) as bonusQuantity'))
                ->groupBy('customer_bones_id');
}

Customer_Bones::with(['getCustomer' => function($query){
                                          $query->select('customer_name','customer_id');
                                       }
, 'getBonusCustomersSum'])
->get();

Laravel use multiple where and sum in single clause

@Khalid first method work like charm with some edit. Thank you very much. But second method i already tried, but result is null. — Pushpamal, Aug 09 '18 at 05:56
This line `$query->select(DB::raw('SUM(bonus_quantity)'));` Helped :) — Qazi, Apr 27 '19 at 06:47

How to use SUM() with hasMany result from Model in Laravel 5.6

1 Answers1