Apr 24, 2019

Level 3

Query Builder - Sum with groupBy on relationship

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

jlrdw

7 years ago

Level 75

quote

->get()
    ->groupBy('transactionType.category');

unquote

Why do you have a groupby after a get?

This works, maybe adjust to your data:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

https://laravel.com/docs/5.8/queries

But you could also set up eloquent relations if you want to:

https://laravel.com/docs/5.8/eloquent

https://laravel.com/docs/5.8/eloquent-relationships

I'd suggest viewing some videos on some of these various topics.

booni3

7 years ago

Level 3

@jlrdw this gives the groupBy method on the collection... and it provides an array with my categories as keys and transactions nested within. https://laravel.com/docs/5.8/collections#method-groupby

It kind of works, but ideally I do not want my results nested. So it kind of works but I want to get the groupBy in the query builder.

Simply moving this before the get fails, as does putting the groupBy inside the call back, as you are supposed to do with a group on a relationship column.

edit:

Using a join as you suggested, does work. Is this the way to do it though? I thought the more 'eloquent' way used a callback in the with.

    ->with(['transactionType' => function($query){
        $query->select('category')->groupBy('category');
    }])

this is generally how I have done it, but its not working with this aggregate.

jlrdw

7 years ago

Level 75

See updated answer.

booni3

7 years ago

Level 3

I have eloquent relations setup already, as shown in the original post. I am trying to get this to work with the relationship rather than with the join, but it seems the aggregate is messing it up in this case.

The relationship works as intended as I am using it elsewhere, but not in this query.

Is it maybe because I am aggregating the 'transactionType' relationship also, on the category column?

jlrdw

7 years ago

Level 75

@LAMBOONI - To me, and just my opinion, there are times to use active record and times not to. I restrict usage to simple relations, like a one to many. For more complex stuff I either write a regular query or use eloquent but with query builder.

Sorry I did not have a better answer. But what I showed is how I would tackle that query.

Please or to participate in this conversation.