lambooni
6 months ago

Query Builder - Sum with groupBy on relationship

Posted 6 months ago by lambooni

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

Please sign in or create an account to participate in this conversation.