Level 104
What is $order[0] and orderlines[0] in this scenario?
Feb 2, 2021
9
Level 2
Build Eloquent query with child aggregate
Hello All
Is it possible to build an eloquent query such that I get the parent, and then children as a sum aggregate. so,
Order:
1
2
3
OrderLines:
type value
A 10
A 12
B 10
Result :
$order[0]->orderlines[0]->type would equate to A
$order[0]->orderlines[0]->value would equate to 22
$order[0]->orderlines[1]->type would equate to B
$order[0]->orderlines[1]->value would equate to 10
Thanks
Level 2
Sorry... the idea was if I do a dd($order[0]->orderlines[1]) that would be the result.
I hope that makes sense.
Level 104
It doesn't.
Anyway, there are many ways to achieve this; but first it depends on the nature of the relationship between Order and OrderLine models which is not clear above.
Level 2
The relationship is a 1-n pk=id, fk=order_id
Level 104
In that case, assuming you have an orderitems hasMany relation on the Order model, the following should work:
$orders => Order::with(['orderitems' => function ($builder) {
$builder->selectRaw('sum(value) as value, type')->groupBy('type');
}])->get();
Now, you can use your preferred syntax:
$orders[0]->orderitems[0]->type;
$orders[0]->orderitems[0]->value;
Level 2
Thanks for the code.
Is there a way to do it without raw. In my case value in the child table is an accessor?
Level 104
FFS. That is not the problem you described originally. Why would you omit an important piece of information such as the nature of the source data
Level 2
I assumed eloquent query would mean that ?
Level 2
Okay... I did it. not the most elegant way but it works.. I ended up creating an array of type and sum of values.
@php
$vats = array();
foreach($orderhdr->orderlis as $orderli){
$key = $orderli['ordervattype_id'];
if (!array_key_exists($key, $vats)) {
$vats[$key] = array(
'id' => $orderli['ordervattype_id'],
'vat' => $orderli['vat']
);
} else {
$vats[$key]['vat'] = $vats[$key]['vat'] + $orderli['vat'];
}
}
@endphp
Please or to participate in this conversation.