CODEWITHSUNDEEP
phpmysqllaraveleloquent
Diwash v1
Diwash v1

Reputation: 124

Laravel Eloquent - How Can I Sum fields inside relation's column?

  I need to sum every paid_amount inside of each expense_payment_liab instead of returning all these 
 individual  paid_amount  as it can be seen in images for clear view.So far my query looks like this:
 Model Relation is like Voucher has many Expenses and Expenses has many Expensepaymentliab.       

  This is so far what I tried:
    $expense = Voucher::where('voucher_date', $request->date)
        ->where('account_id', 1)
        ->with(['expenses' => function ($expense) use ($date) {
            $expense->with(['expPaymentLiab' => function ($inner) use ($date) {
                return $inner->select('paid_amount', 'expense_id')->where('paid_date', '=', $date);
                //need to return the sum of paid_amount.
                // I've  also tried this
                 //$inner->sum('paid_amount')
                // $inner->sum('paid_amount', 'expense_id')
             }]);
            $expense->select('id', 'date', 'total_amount', 'voucher_id');
        }])->get();

These are the images please check

Need to sum such paid_amount field.

Upvotes: 1

Views: 1455

Answers (1)

TsaiKoga
TsaiKoga

Reputation: 13394

You can use withCount to sum the paid_amount field.

Voucher::where('voucher_date', $request->date)
        ->where('account_id', 1)
        ->with(['expenses' => function ($expense) use ($date) {
            // select the columns first, so the subquery column can be added later.
            $expense->select('id', 'date', 'total_amount', 'voucher_id');
            $expense->withCount(['expPaymentLiab AS paid_sum' => function ($query) use ($date) {
                return $query->select(DB::raw('SUM(paid_amount)'))->where('paid_date', '=', $date);
             }]);
        }])
        ->get();

Upvotes: 1

Related Questions