Level 104
Well, start with eager-loading... An Invoice has many InvoiceDescriptions
$invoice = Invoice::with('description')->//...
Otherwise, please format your code
Dec 13, 2021
19
Level 1
How to optimize this query in laravel
Thanks in advance. I want to optimize below query because when it applies for thousands of data then thousands for models also precessed by using below query. Can anyone suggest solution for below query.
@foreach($invoice as $inv)
@foreach(App\Models\invoicedescription::where('invoice_id',$inv->id)->get() as $invdes )
{
$dat += $invdes->qty*$invdes->rate;
}
@endforeach
@endforeach
Level 73
Move the query to your controller and as @tykus says, start with eager loading the invoice description.
1 like
Level 88
Well, you can use relationships here to query everything at once. Note that this only works if you actually have a invoicesDescriptions relationship set up.
$invoices = Invoices::with('invoicesDescriptions')->get();
$dat = 0;
@foreach ($invoices as $invoice)
@foreach ($invoice->invoicesDescriptions as $invdes)
$dat = $invdes->qty * $invdes->rate;
@endforeach
@endforeach
Instead of looping, you can also use the collection methods to achieve the same.
$invoices = Invoices::with('invoicesDescriptions')->get();
$dat = $invoices->sum(function ($invoice) {
return $invoice->invoicesDescriptions->sum(function ($invdes) {
return $invdes->qty * $invdes->rate;
});
});
1 like
Level 1
@bobbybouwmann Thanks for your effort. Your 1st suggestion works fine but create too many models. Can you suggest "How to reduce the number of models"?
Level 104
@mkumar2001 what are you actually trying to achieve here? What is $dat, and what do you do with it?
1 like
Level 1
@tykus Okay, I am explaining you in a simple way- there is a table named invoice which contains(id,invoicenumber) and another table invoicedescription which contains(invoice_id,qty,rate,tax). I want sum of each invoice.
Level 88
@mkumar2001 Well, to get the data you need to loop over all the records. You can also query them using SQL to get the count.
But in the end, the questions remain the same. Do you only need the count or do you need more info from the invoices?
1 like
Level 104
@mkumar2001 I'll ask a question in a simple way then - why are you performing the calculation in PHP when the database would be much more efficient?
1 like
Level 1
@bobbybouwmann Actually I want some more info but the main concern for sum of each invoice to displayed in front of invoice number.
Level 1
@tykus I want to display the details of invoicedescription data then sum of each invoice in front of invoice number. I can not insert sum of each invoice in invoice table during generation of bills.
Level 104
@mkumar2001 But you can compute it.
Invoice::query()
// adds a `invoice_total` property on each invoice
->selectRaw('invoices.*, coalesce(sum(qty * rate), 0) as invoice_total')
// eager-loads the relations
->with('invoiceDescription')
->leftJoin('invoice_descriptions', ('invoices.id', 'invoice_description.invoice_id')
->get();
Level 1
@tykus Thanks for suggestion, but below is my actual code-
$invoice = Invoice::with('getInvdes')->get();
@foreach($invoice as $p=>$inv)
@php($tot=0)
@php($ptot=0)
@php($cgst=0)
@php($sgst=0)
@php($igst=0)
@foreach($inv->getInvdes as $pri)
@if($pri->quantity > 0 && $pri->mindimen > 0 && $pri->maxdimen > 0)
@php($igst = $pri->mindimen*$pri->maxdimen*$pri->rate*$pri->quantity*$pri->igst/100)
@php($cgst = $pri->mindimen*$pri->maxdimen*$pri->rate*$pri->quantity*$pri->cgst/100)
@php($sgst = $pri->mindimen*$pri->maxdimen*$pri->rate*$pri->quantity*$pri->sgst/100)
@php($tot += $pri->mindimen*$pri->maxdimen*$pri->rate*$pri->quantity+$cgst+$sgst+$igst)
@else
@php($igst = $pri->quantity*$pri->rate*$pri->igst/100)
@php($cgst = $pri->quantity*$pri->rate*$pri->cgst/100)
@php($sgst = $pri->quantity*$pri->rate*$pri->sgst/100)
@php($tot += $pri->quantity*$pri->rate+$igst+$cgst+$sgst)
@endif
@endforeach
@endforeach
This returns data perfectly, It returns less number of query but very huge number of models more than 7000 models.
Level 104
@mkumar2001 can't make that out at all.. can you put some effort into formatting your code?
Level 1
@tykus Please check this above is the formatted code now.
Level 104
@mkumar2001 does your math make sense? Cryptic variable name don't help...
$igst = $pri->mindimen*$pri->maxdimen*$pri->rate*$pri->quantity*$pri->igst/100
$tot += $pri->mindimen*$pri->maxdimen*$pri->rate*$pri->quantity+$cgst+$sgst+$igst
1 like
Level 122
@mkumar2001 which is why you should calculate aggregates in sql not php
1 like
Level 1
@tykus Can you suggest solution?
Level 1
Thanks all of you for your efforts. But my issue still remain same. Can anyone suggest how to reduce the number of model?
Level 75
See https://www.mysqltutorial.org/mysql-aggregate-functions.aspx
As others suggest use aggregate functions.
I do monthly reports using aggregates.
1 like
Please or to participate in this conversation.