Level 88
What have you tried so far? I think a simply group by school_id and category_id is already enough. After that you only need to use a raw query and sum the amount column
Feb 26, 2018
4
Level 3
Eloquent relationship query, sum(), group, count().
Hi everyone, let's say we have three models, schools, transactions, transaction_categories.
my database is:
schools table: id, name
transactions table: id, school_id, category_id, amount
transaction_categories table: id, name
In transaction categories I have one, two, three, four. In transactions, transactions associated with school.
I need to group transactions by category_id and then sum. The output I need is index (school id) and then all categories.
[
1 => {
"one" : 450,
"two" : 1000,
"three" : 450,
},
2 => {
"one" : 450,
"two" : 1000,
"three" : 450,
},
3 => {
"one" : 450,
"two" : 1000,
"three" : 450,
},
]
Any ideas? I have tried fr hours with no success.
Level 75
First, eloquent has the query builder functions built in. So by doing a study of a previous answer of how a complex query is built up will help, see
https://laracasts.com/discuss/channels/eloquent/orderby-computed-related-attribute
But if you study the query builder docs, you will get the hang of it.
Me, for complex type queries I prefer getPdo() with normal sql, that's just me.
Level 3
Thank you for your help @bobbybouwmann @jlrdw I ended up studying both eloquent and the query builder.
I ended up with this result:
Collection {#418 ▼
#items: array:7 [▼
2 => Collection {#425 ▼
#items: array:11 [▼
"cash-out" => "12310.06"
"merchandise" => "12197.00"
"new" => "25724.05"
"other" => "7918.62"
"pending" => "4239.10"
"pre-paid-test" => "2453.00"
"private-class" => "594.30"
"renewal" => "15393.86"
"special-activity" => "8546.00"
"testing" => "10336.00"
"upgrade" => "8880.00"
]
}
3 => Collection {#424 ▼
#items: array:11 [▼
"after-school" => "1185.00"
"cash-out" => "7693.80"
"merchandise" => "15236.50"
"new" => "10555.00"
"other" => "5714.93"
"pre-paid-test" => "2370.00"
"private-class" => "180.00"
"renewal" => "4228.60"
"special-activity" => "565.00"
"testing" => "8280.00"
"upgrade" => "6638.66"
]
}
4 => Collection {#423 ▶}
6 => Collection {#422 ▶}
7 => Collection {#421 ▶}
8 => Collection {#420 ▶}
9 => Collection {#419 ▶}
]
}
From this query:
$transactions = \App\Transaction::has('school')
->select('school_id', 'reason_id', DB::raw('sum(paid) as total'), 'name as reason_name')
->join('transaction_types', 'transaction_types.id', '=', 'transactions.reason_id')
->groupBy(['school_id', 'reason_name'])
->get()
->groupBy(['school_id'])
->map(function ($item, $key) {
return $item->pluck('total', 'reason_name');
});
1 like
Level 88
Cool!
Please or to participate in this conversation.