Laravel:如何使用Eloquent获取关系列的SUM [英] Laravel: How to get SUM of a relation column using Eloquent

问题描述

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

在我的项目中，有两个模型，分别是 Account 和 Transaction .帐户模型有很多交易.

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.

提供了我当前的代码:在此代码中，急需加载 transactions 并使用php计算总和.但是我不希望加载整个事务.唯一的要求是 sum('amount').

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使用:

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

推荐答案

您需要子查询才能执行此操作.我将向您展示一些解决方案:

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

• 解决方案1 ​​

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

解决方案2

Solution 2

为EloquentBuilder创建一个 withSum 宏.

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

然后，可以像使用 withCount 一样使用它，除了需要添加需要在关系之后求和的列( relation.column )

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

这篇关于Laravel:如何使用Eloquent获取关系列的SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！