Can I see LoanRetrievalServices::getNextPayDate() function?
Does this count as a example of n+1 query and can it be improved?
A little background, I have a table of debtors who pay back there loans periodically at different frequencies (daily, weekly, monthly). the code below is a going to be used by a cron job to update the next payment date for each loan depending on the payment frequency of each loan. I want to ask if this is an example of an N+1 query of some sort, and can I optimize this better performance wise?
public function handle(LoanRetrievalServices $loan_retrieval)
{
$loan_schedule = LoanRetrievalSchedule::whereDate('next_payment_date', Carbon::today());
$loan_schedule->each(function ($item) use ($loan_retrieval) {
$next_payment_date = $loan_retrieval->getNextPayDate($item->repayment_frequency);
$item->update([ 'next_payment_date' => $next_payment_date ]);
});
}
##getNextPayDate function:
public function getNextPayDate($frequency)
{
switch ($frequency) {
case 'daily':
$next_payment_date = Carbon::now()->addWeekday();
$holidays = Holiday::select('day_date')->where('active', 1)->get()->toArray();
foreach ($holidays as $holiday) {
if ( $next_payment_date->toDateString() == $holiday ) {
$next_payment_date->addWeekday();
}
}
break;
case 'weekly':
$next_payment_date = Carbon::now()->addWeek();
$holidays = Holiday::select('day_date')->where('active', 1)->get()->toArray();
foreach ($holidays as $holiday) {
if ( $next_payment_date->toDateString() == $holiday ) {
$next_payment_date->addWeekday();
}
}
break;
case 'monthly':
$next_payment_date = Carbon::now()->addMonth();
$holidays = Holiday::select('day_date')->where('active', 1)->get()->toArray();
foreach ($holidays as $holiday) {
if ( $next_payment_date->toDateString() == $holiday ) {
$next_payment_date->addWeekday();
}
}
break;
default:
# code...
break;
}
return ($next_payment_date->toDateTimeString());
}
}
First of all, make sure that it's N+1 issue, then you can optimize easily.
I better suggest you install debugbar in your project and check how many queries are there actually.
Please or to participate in this conversation.