Multiple sum on one query in Eloquent

Question

I am trying to speed up my application and have a question about performing actions on a single Eloquent query.

I need to draw a table containing different totals.

        $records = $this->finance
                        ->where('company_id','=',$this->company_id)
                        ->where(DB::raw('MONTH(date)'), '=', $month['month'])
                        ->where(DB::raw('YEAR(date)'), '=', $year)
                        ->where('financeaccounts_id', '=', $account);

        $income = $records->where('type','=','Income')->sum('amount');
        $expense = $records->where('type','=','Expense')->sum('amount');
        $correction = $records->where('type','=','Correction')->sum('amount');

'Income' is being calculated correctly, however the subsequent 'sums' aren't. I am guessing that the query is being modified with each assignment. I'd appreciate your help. Thanks

Amit Gupta · Answer 1 · 2017-01-13T15:54:27.663

0

Yes, you are right that query is modified. To solve this issue you can clone the query as:

$income_records = clone $records;
$expense_records = clone $records;
$correction_records = clone $records;

$income = $income_records->where('type','=','Income')->sum('amount');
$expense = $expense_records->where('type','=','Expense')->sum('amount');
$correction = $correction_records->where('type','=','Correction')->sum('amount');

edited Jan 13 '17 at 15:54

answered Jan 03 '17 at 17:16

Amit Gupta

17,072
4
41
53

Thanks Amit, apprciated – user3459394 Jan 03 '17 at 17:51

score 0 · Answer 2 · answered Jan 03 '17 at 17:31

I think better way would be running just one query instead of 3 and then work with collection:

$records = $this->finance
    ->where('company_id','=',$this->company_id)
    ->where(DB::raw('MONTH(date)'), '=', $month['month'])
    ->where(DB::raw('YEAR(date)'), '=', $year)
    ->where('financeaccounts_id', '=', $account)
    ->get();

    $income = $records->where('type', 'Income')->sum('amount');
    $expense = $records->where('type', 'Expense')->sum('amount');
    $correction = $records->where('type', 'Correction')->sum('amount');

In this code you're using sum() collection method instead of query builder's sum() method. This approach will not create three queries, but just one.

@Alexey Mezenin could you help me on this post? `https://stackoverflow.com/q/51226415/1830228` thanks — DolDurma, Jul 07 '18 at 20:09

score 0 · Answer 3 · answered Jan 03 '17 at 17:56

You could also use subqueries to do it all in one query:

$records = $this->finance
                    ->select('finances.*')
                    ->selectSub(function($query) {
                        return $query->selectRaw('SUM(amount)')
                            ->where('type', '=', 'Income');
                    }, 'income_sum')
                    ->selectSub(function($query) {
                        return $query->selectRaw('SUM(amount)')
                            ->where('type', '=', 'Expense');
                    }, 'expense_sum')
                    ->selectSub(function($query) {
                        return $query->selectRaw('SUM(amount)')
                            ->where('type', '=', 'Correction');
                    }, 'correction_sum')
                    ->where('company_id','=',$this->company_id)
                    ->whereMonth('date' $month['month'])
                    ->whereYear('date' $year)
                    ->where('financeaccounts_id', '=', $account)
                    ->get();                  

$income = $records->income_sum;
$expense = $records->expense_sum;
$correction = $records->correction_sum;

Multiple sum on one query in Eloquent

3 Answers3