php - Laravel：如何使用 Eloquent 获取关系列的总和

Question

如何在不加载整个关系数据的情况下使用预加载获取相关模型的 SUM？

在我的项目中有两个模型，Account和Transaction. 账户模型has many交易。

我的要求是：获取帐户并只加载相关表上的总和。

提供了我当前的代码：在此代码transactions中急切加载并使用 php 计算总和。但我宁愿不加载整个交易。唯一的要求是sum('amount')。

表：帐户

| id | name | address | ...

表：交易

| id | account_id | amount | ...

账户.php

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

以下代码给出了每个帐户及其交易。

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

SUM 使用以下公式计算：

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

我已经尝试过这样的事情，但没有奏效。

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

标签： phplaravellaravel-5.8

Answer 1

你需要子查询来做到这一点。我会告诉你一些解决方案：

• 解决方案 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;
  }
  

• 解决方案 2

  为 EloquentBuilder创建一个withSum宏。

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

  然后，您可以像使用 时一样使用它withCount，只是需要在关系 ( ) 之后添加需要求和的列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();