CODEWITHSUNDEEP
laraveleloquent
Nite
Nite

Reputation: 383

Eloquent sum of foreign key column

The models I have are : Invoice - Credit_note - Payment

What I need to do is to calculate the sum of the Payments(amount) of the Credit_notes of an Invoice.

With the relations I currently have in place, I can load all the Credit_notes (with their Payments) of an Invoice.

Credit_note::with(['payment'])->where('invoice_id', $i->id)

I could then sum the amounts by hand... but, is there a way to directly return the sum with the query ?

I tried to add a ->sum('payment.amount') or something similar, with no luck.

Thanks.

Upvotes: 0

Views: 820

Answers (3)

Safin Mohammed
Safin Mohammed

Reputation: 21

Inside Credit_note model make this function 

public function sum_of_payment(){
    $result=0;
    $paiments=$this->payment()->get();

    foreach($paiments as $paiment){
        $result+=$paiment->amount;
    }
    return $result;

}

then you can get SUM like this 

$sum=Credit_note::with(['payment'])->where('invoice_id', $i->id)->first()->sum_of_payment();

Upvotes: 0

Nite
Nite

Reputation: 383

Solution :

$sum = Credit_note::with(['payment:id,amount'])
            ->where('invoice_id', $i->id)
            ->get()
            ->pluck('payment.amount')
            ->sum();

Upvotes: 0

OMR
OMR

Reputation: 12188

$sum = Credit_note::with([
        'payments' => function ($query) {
            $query->selectRaw('payment_id, SUM(payments.amount) AS cn_total')->groupBy('payment_id');
        },
    ])
    ->where('invoice_id', $i->id)->get()->pluck('payments.cn_total')->sum();

Upvotes: 1

Related Questions