Query Optimization, changing the queries in the loop into a single processing query

Question

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

Answer 1

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

Answer 2

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.

Answer 3

@faesal with your answer I changed it to have some arrays as ID's

$applicantIds = \DB::select('id')->from( 'applicants' )->where( 'created_at', 0 )->execute()->as_array('id');

$applicantApps = \DB::select( 'applicant_id', 'created_at' )->from( 'applicant_apps' )->where( 'applicant_id', 'in', array_keys($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';";
}

\DB::query($statement)->execute();