How do I optimize these queries to be one or two? The one in the loop to be one at least.
$applicantIds = \DB::select()->from( 'applicants' )->where( 'created_at', 0 )->execute();
foreach ($applicantIds as $applicantId)
{
$applicantApp = \DB::select( 'applicant_id', 'created_at' )->from( 'applicant_apps' )->where( 'applicant_id', $applicantId['id'] )->execute();
\DB::update( 'applicants' )->value( 'created_at', $applicantApp[0]['created_at'] )->where( 'id', $applicantApp[0]['applicant_id'] )->execute();
}
CodePudding user response:
I I understand you, you can use singe query for this:
update applicants a
join applicant_apps aa on aa.applicant_id = a.id
set a.created_at = aa.created_at
where a.created_at = 0;
When you want to use Laravel syntax:
$db::table('applicants as a')
->where( $db::raw("`a`.`created_at`"), 0)
->join('applicant_apps as aa', 'aa.applicant_id', '=', 'a.id')
->update([ 'a.created_at' => $db::raw("`aa`.`created_at`") ])
;
CodePudding user response:
Try this:
$applicantIds = \DB::select()->from( 'applicants' )->where( 'created_at', 0 )->execute();
$applicantApps = \DB::select( 'applicant_id', 'created_at' )->from( 'applicant_apps' )->where( 'applicant_id', 'in', $applicantIds)->execute();
$statement = '';
foreach ($applicantApps as $applicantApp)
{
$applicantId = $applicantApp['applicant_id'];
$applicantCreatedAt = $applicantApp['created_at'];
$statement .= "update applicants set created_at='$applicantCreatedAt' where id = '$applicantId';"
}
if ($statement) {
\DB::query($statement)->execute();
}
Two queries only will get you this.
And you can add some logic in foreach block if you need.
CodePudding user response:
I settled on having one query outside the loop and one inside the loop and it worked. Thanks for everyone who contributed.
$applicantIds = \DB::select()->from( 'applicants' )->where( 'created_at', 0 )->execute();
foreach ($applicantIds as $applicantId) {
\DB::query('UPDATE applicants
SET created_at = ( SELECT created_at FROM applicant_apps WHERE applicant_id = '.$applicantId['id'].' )
WHERE created_at = 0')->execute();
}