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;
}

Answer 1

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();
  

Answer 2

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;
}