sum of columns in another table - laravel

Question

There's a loan table and loan_addition table. How can I sum the added_amt of loan_addition table and show them while returning loan table data? loan_addition table

id   loan_id   added_amt
1       1        100
2       1        200

controller

$data = Loan::select('*')
->with('loanAddition') // how to sum all the columns of respective loan_id here
->get();
dd($data);

loan model

class Loan extends Model
{
    public function loanAddition() {
        return $this->hasMany(LoanAddition::class);
    }
}

LoanAddition model

class LoanAddition extends Model
{
    public function loan()
    {  return $this->belongsTo(Loan::class);  }
}

Thanks in advance..

BABAK ASHRAFI · Accepted Answer · 2021-05-30 16:48:33Z

3

You can use withSum() method as stated here

$records = Loan::select('*')->withSum('loanAddition', 'loan_id')->get();

Then get the data like below:

$records->pluck('loanAdditions_sum_loan_ids');

Update: for laravel below 8.12

Loan::select('*')->withCount(['loanAddition as loan_id'=>function($query){
        $query->select(DB::raw("SUM(loan_id) as loan_id"));
    }])->get()

edited May 30, 2021 at 16:48

answered Feb 28, 2021 at 7:35

BABAK ASHRAFI

4,3114 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amrita Stha Over a year ago

Its throwing an error - Call to undefined method Illuminate\Database\Eloquent\Builder::withSum()

apokryfos Over a year ago

@AmritaStha this needs Laravel 8.12 and above

DOLWIN DAVIS · Accepted Answer · 2021-02-28 07:41:07Z

0

$data = Loan::select('*')
->with(['loanAddition',function($query){
      $query->select('sum(added_amt) as sum_added_amt')
 }]) // how to sum all the columns of respective loan_id here
->get();
dd($data);

or you can do is $data->first()->sum('loanAddition.added_amt) where you want sum

answered Feb 28, 2021 at 7:41

DOLWIN DAVIS

912 bronze badges

2 Comments

Amrita Stha Over a year ago

Column not found: 1054 Unknown column 'sum(added_amt)'

Amrita Stha Over a year ago

Instead I've used the following as well but it return empty - $q->select(DB::raw('sum(added_amt) as sum_added_amt'));

Collectives™ on Stack Overflow

sum of columns in another table - laravel

2 Answers 2

Update: for laravel below 8.12

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update: for laravel below 8.12

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related