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`") ])
     ;

Online test

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();
}