Laravel雄辩的查询构建器 - 与关系上的组相加

Question

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

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   |

Table: transactions

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

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

i.e.

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

Answer 1

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

Or Less Eloquent

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