Laravel雄辩的查询生成器-与关系组的总和 [英] Laravel eloquent query builder - Sum with group on relationship

问题描述

我正在寻找以下方面的查询构建器解决方案:

I am looking for a query builder solution for the following:

表:transaction_types

Table: transaction_types

| id | type_hash | description     | category |
|----|-----------|-----------------|----------|
| 1  | abcd      | sale price      | sale     |
| 2  | dbac      | sale tax        | sale     |
| 3  | agft      | sale shipping   | sale     |
| 4  | pgsk      | refund price    | refund   |
| 5  | sa2r      | refund tax      | refund   |
| 6  | sdf4      | refund shipping | refund   |

表:交易

| id | type_hash | amount |
|----|-----------|--------|
| 1  | abcd      | 12     |
| 2  | dbac      | 14     |
| 3  | agft      | 19     |
| 4  | pgsk      | -20    |
| 5  | sa2r      | -12    |
| 6  | sdf4      | -7     |

关系-交易属于交易类型

Relationship - transaction belongs to transaction type

public function transactionType() : BelongsTo
{
    return $this->belongsTo(TransactionType::class, 'type_hash', 'type_hash');
}

我要在交易表上查找的结果是:

The result I am looking for on the transactions table is:

• 金额总计 sum(amount)作为金额
• 按 TransactionType.category
分组交易

• Amount aggregated sum(amount) as amount
• Group transactions by TransactionType.category

即

| Results | transactionType.category | sum(amount)   |
|---------|--------------------------|---------------|
| 1       | sale                     | 45            |
| 2       | refund                   | -39           |

我可以完成以下工作，但理想情况下，我想在查询构建器中而不是在集合中进行所有聚合:

I can get the following working, but ideally I want to do all the aggregation in the query builder, not in the collection:

Transaction::selectRaw('sum(amount) as amount')
    ->with('transactionType')
    ->get()
    ->groupBy('transactionType.category');

我已经尝试了以下方法(及其变体)，但无法使其正常工作:

I have tried the following (and variations of), but cannot get it working:

Transaction::selectRaw('sum(amount) as amount')
    ->with(['transactionType' => function($query){
        $query->select('category')->groupBy('category');
    }])
    ->get();

推荐答案

在生成的SQL中，您需要选择要分组的列，并且需要在 groupBy ，否则您将在集合上调用 groupBy ，而不是查询构建器对象.因此，您应该可以:

In the generated SQL, you need to select the column you group by, and you need to call the get() after the groupBy or else you'd be calling the groupBy on the collection, not the query builder object. So you should be able to do:

Transaction::selectRaw('transactionType.category, sum(amount) as amount')
->with('transactionType')
->groupBy('transactionType.category')
->get();

或不太口才

DB::table('transaction')
->join(
    'transaction_type',
    'transaction_type.id',
    '=',
    'transaction.transaction_type_id'
)->selectRaw('transationType.category, sum(amount)')
->groupBy('transactionTyle.category')
->get();

这篇关于Laravel雄辩的查询生成器-与关系组的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！