查询优化，将循环中的查询更改为单个处理查询

Question

如何优化这些查询是一两个？循环中的一个至少是一个。

$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

尝试以下操作：

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

两个查询只会为您带来这个问题。

如果需要，您可以在foreach块中添加一些逻辑。

Answer 2

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

Answer 3

@faesal带有您的答案，我将其更改为有一些数组作为ID

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