Laravel: How to get SUM of a relation column using Eloquent

Question

How to get SUM on related model using eager loading, without loading whole relation data?

In my project there are two models, Account and Transaction. Account model has many transactions.

My requirement is : Get accounts and eager load only the sum on the related table.

My current code is provided : In this code transactions are eager loaded and sum is calculated using php. But I would prefer not to load the whole transactions. The only requirement is sum('amount').

table : accounts

| id | name | address | ...

table : transactions

| id | account_id | amount | ...

Account.php

/**
 * Get the transaction records associated with the account.
 */
public function transactions()
{
    return $this->hasMany('App\Models\Transaction', 'account_id');
}

The following code gives each accounts and its transactions.

$account = Account::with(['transactions'])->get();

SUM is calculated using :

foreach ($accounts as $key => $value) {
    echo $value->transactions->sum('amount'). " <br />";
}

I have tried something like this, but didn't work.

public function transactions()
{
    return $this->hasMany('App\Models\Transaction', 'account_id')->sum('amount;
}

nmfzone · Accepted Answer · 2019-11-02 05:43:41Z

You need sub query to do that. I'll show you some solution:

Solution 1

$amountSum = Transaction::selectRaw('sum(amount)')
    ->whereColumn('account_id', 'accounts.id')
    ->getQuery();

$accounts = Account::select('accounts.*')
    ->selectSub($amountSum, 'amount_sum')
    ->get();

foreach($accounts as $account) {
    echo $account->amount_sum;
}

Solution 2

Create a withSum macro to the EloquentBuilder.

use Illuminate\Support\Str;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Expression;

Builder::macro('withSum', function ($columns) {
    if (empty($columns)) {
        return $this;
    }

    if (is_null($this->query->columns)) {
        $this->query->select([$this->query->from.'.*']);
    }

    $columns = is_array($columns) ? $columns : func_get_args();
    $columnAndConstraints = [];

    foreach ($columns as $name => $constraints) {
        // If the "name" value is a numeric key, we can assume that no
        // constraints have been specified. We'll just put an empty
        // Closure there, so that we can treat them all the same.
        if (is_numeric($name)) {
            $name = $constraints;
            $constraints = static function () {
                //
            };
        }

        $columnAndConstraints[$name] = $constraints;
    }

    foreach ($columnAndConstraints as $name => $constraints) {
        $segments = explode(' ', $name);

        unset($alias);

        if (count($segments) === 3 && Str::lower($segments[1]) === 'as') {
            [$name, $alias] = [$segments[0], $segments[2]];
        }

        // Here we'll extract the relation name and the actual column name that's need to sum.
        $segments = explode('.', $name);

        $relationName = $segments[0];
        $column = $segments[1];

        $relation = $this->getRelationWithoutConstraints($relationName);

        $query = $relation->getRelationExistenceQuery(
            $relation->getRelated()->newQuery(),
            $this,
            new Expression("sum(`$column`)")
        )->setBindings([], 'select');

        $query->callScope($constraints);

        $query = $query->mergeConstraintsFrom($relation->getQuery())->toBase();

        if (count($query->columns) > 1) {
            $query->columns = [$query->columns[0]];
        }

        // Finally we will add the proper result column alias to the query and run the subselect
        // statement against the query builder. Then we will return the builder instance back
        // to the developer for further constraint chaining that needs to take place on it.
        $column = $alias ?? Str::snake(Str::replaceFirst('.', ' ', $name.'_sum'));

        $this->selectSub($query, $column);
    }

    return $this;
});

Then, you can use it just like when you're using withCount, except you need to add column that need to sum after the relationships (relation.column).

$accounts = Account::withSum('transactions.amount')->get();

foreach($accounts as $account) {
    // You can access the sum result using format `relation_column_sum`
    echo $account->transactions_amount_sum;
}

$accounts = Account::withSum(['transactions.amount' => function (Builder $query) {
    $query->where('status', 'APPROVED');
})->get();

Nice. Please kindly accept the answer if it's answering your question 👌
$records->withCount("invoiceItem"); $records->withSum("invoiceItem", 'quantity'); $records->withSum("fk_item_id", 'selling_price_to');

iamab.in · Accepted Answer · 2019-11-02 19:04:34Z

1

If Account hasMany Transactions, you could use the following query to get the amount

Account::with(['transactions' =>  function( $q) {
    $q->selectRaw('sum(amount) as sum_amount, account_id')->groupBy('account_id');
}

You need to make sure that account_id is selected in the Closure otherwise the relationship would not work.

Alternatively, you could also define another relationship like transactionSums as below in Account Model:

public function transactionSums() {

    return $this->hasMany(Transaction::class)->selectRaw('sum(amount) as sum_amount, account_id')->groupBy('account_id');
}

Then your controller code will be cleaner as below:

$accounts = Account::with(['transactionSums' ]);

foreach($accounts as $account)
{
    echo $account->transactionSums[0]->sum_amount;
}

edited Nov 2, 2019 at 19:04

iamab.in

2,0604 gold badges20 silver badges42 bronze badges

answered Nov 2, 2019 at 6:19

ascsoftw

3,4842 gold badges17 silver badges23 bronze badges

7 Comments

ascsoftw Over a year ago

@nmfzone I am not sure what you are referring to as mess. I have shown 2 ways to achieve the result and code is straightforward.

iamab.in Over a year ago

@ascsoftw Thank you for the answer. I have tested both solutions. Both works. Please note, you have a typo in sum(account), it should be sum(amount). To get the result $account->transactionSum[0]->sumAmount

ascsoftw Over a year ago

@ab_ Glad to help. Sorry about the typo, I misread the column name.

nmfzone Over a year ago

@ascsoftw You're showing how to solve, but not showing how to get the result. Show us how to get the sum of amount on each iteration..

ascsoftw Over a year ago

@nmfzone The sum is present in the object. Its not a big deal to fetch the value from object. The real issue was getting the sum using eager loading.

|

Aken Roberts · Accepted Answer · 2019-11-02 02:14:15Z

0

Whoops, I completely replaced SUM with COUNT in my head. While this will not directly solve your issue, the underlying code for whereCount() may provide some insight.

Counting Related Models

If you want to count the number of results from a relationship without actually loading them you may use the withCount method, which will place a {relation}_count column on your resulting models.

$accounts = Account::withCount('transactions')->get();

foreach ($accounts as $account) {
    $transactionCount = $account->transactions_count;
}

edited Nov 2, 2019 at 2:14

answered Nov 2, 2019 at 1:02

Aken Roberts

13.5k3 gold badges38 silver badges42 bronze badges

1 Comment

iamab.in Over a year ago

Thank you for the reply. I was looking for withSum(). 😊 Something like withCount().

Collectives™ on Stack Overflow

Laravel: How to get SUM of a relation column using Eloquent

3 Answers 3

2 Comments

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related