Level 1
using count()
1 like
Sep 20, 2022
9
Level 1
How to group by in multi relational table
here is my code:
Invoice::select('id', 'date')
->with('invoice_items:invoice_id,item_id,amount', 'invoice_items.item_title:id,name')
->groupBy('item_id') // invoice item id
->groupBy('amont') // item price
->get();
I want same invoice item id will be grouped and SUM() price. but I don't understand how do i do this. thanks.
Level 102
Does any of those relationships require the id of the invoice? It sounds like Invoice_items needs it.
You can't select it in a group by https://sinnbeck.dev/posts/laravel-groupby-error
Level 1
@Sinnbeck it works fine in one table. but when i join two or multi table in relation. this is not working.
Level 102
@mahbubrn there are no joins in the shown code? Do you mean with()? And can you answer my question?
Level 1
- " Do you mean with()?" -- yes
- "Does any of those relationships require the id of the invoice?" -- yes.
Level 102
@mahbubrn ok so simple example
You have two rows with different id. You group by item_id which is the same for them. Which Invoice_items should it pick? Those for id 1 or id 2?
Level 1
@Sinnbeck In this case the item_id will merge and the price will SUM()
example:
1. item_id = 1, price: 10 (main invoice id: 1)
2. item_id = 1, price: 20 (main invoice id: 2)
so I want that it gives me item_id = 1, and price will total 30
Level 102
@mahbubrn yes but I am asking about Invoice_items. You said they pointed to the id on invoices?
Level 1
Guys! finally I solved my problem!
$invoices = Invoice::select('id', 'date', 'type')
->with('invoice_items:invoice_id,item_id,amount', 'invoice_items.item_title:id,name')
->latest('id')
->get();
foreach($invoices as $invoice)
{
echo date('d.M.Y', strtotime($invoice->date)) . "--- <br/> ";
$items = $invoice->invoice_items->groupBy('item_id');
foreach($items as $item)
{
echo $item[0]['item_title']['name'] . ' - ' . $item->sum('amount') . "<br/>";
}
echo "<br/><br/>";
}
Please or to participate in this conversation.