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:
sum(amount) as amount
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();
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();