Laravel 9中高效批量处理选项记录的查询优化方案

场景说明 #

我们需要处理问卷选项的批量增删改操作，现有原始待更新记录结构如下：

array:3 [ // app\Http\Controllers\AdminController.php:245
  "qid" => "5"
  "qname" => "Which of the following pizza toppings do you like?"
  "options" => array:5 [
    7 => "Pepperoni"
    8 => "Mushrooms"
    9 => "Anchovies"
    10 => "Sausage"
    11 => "Artichoke hearts"
  ]
]

前端提交的请求数据包含需更新的选项、新增选项、待删除选项：

array:3 [ // app\Http\Controllers\AdminController.php:245
  "qid" => "5"
  "qname" => "Which of the following pizza toppings do you like?"
  "options" => array:3 [
    7 => "Pepperonis"
    9 => "Anchovies"
    10 => "Sausage"
  ]
  "newoptions" => array:2 [
    0 => "Black olives"
    1 => "Fresh garlic"
  ]
  "deletedoptions" => array:2 [
    0 => 8
    1 => 11
  ]
]

期望操作完成后，最终的选项结构为：

array:3 [ // app\Http\Controllers\AdminController.php:245
  "qid" => "5"
  "qname" => "Which of the following pizza toppings do you like?"
  "options" => array:5 [
    7 => "Pepperonis"
    9 => "Anchovies"
    10 => "Sausage"
    12 => "Black olives"
    13 => "Fresh garlic"
  ]
]

常规单条循环操作会产生过多查询，以下是Laravel 9中的优化方案：

优化方案 #

1. 批量更新现有选项 #

使用upsert或原生CASE WHEN语句，一次性完成所有选项的更新，避免循环执行单条更新：

// 整理批量更新数据
$updateItems = collect($request->options)->map(function ($name, $id) use ($request) {
    return [
        'id' => $id,
        'name' => $name,
        'qid' => $request->qid
    ];
})->toArray();

// 用upsert批量更新（存在则更新，不存在则插入，这里仅用于更新）
\DB::table('options')->upsert(
    $updateItems,
    ['id'], // 唯一匹配字段
    ['name'] // 需要更新的字段
);

// 或者用原生CASE WHEN实现更灵活的批量更新
$caseSql = collect($request->options)->reduce(function ($sql, $name, $id) {
    return $sql . "WHEN id = {$id} THEN '{$name}' ";
}, '');

\DB::table('options')
    ->whereIn('id', array_keys($request->options))
    ->where('qid', $request->qid)
    ->update(['name' => \DB::raw("{$caseSql} ELSE name END")]);

2. 批量删除待删选项 #

直接通过whereIn一次性删除所有目标记录，无需循环删除：

\DB::table('options')
    ->whereIn('id', $request->deletedoptions)
    ->where('qid', $request->qid) // 增加关联过滤，避免误删其他问卷的选项
    ->delete();

3. 批量插入新选项 #

使用insert或createMany批量插入新选项，减少插入查询次数：

// 整理新增数据
$newItems = collect($request->newoptions)->map(function ($name) use ($request) {
    return [
        'qid' => $request->qid,
        'name' => $name
    ];
})->toArray();

// 用Query Builder批量插入，返回第一个新增ID
$firstNewId = \DB::table('options')->insertGetId($newItems);

// 若需获取所有新增ID，推荐使用Eloquent模型的createMany
$newOptions = \App\Models\Option::createMany($newItems);
$newOptionIds = $newOptions->pluck('id')->toArray();

4. 利用Eloquent关联简化操作 #

如果已定义Question与Option的一对多关联（Question hasMany Option），可直接通过关联对象操作，代码更简洁安全：

$question = \App\Models\Question::findOrFail($request->qid);

// 批量更新关联选项
$question->options()->whereIn('id', array_keys($request->options))->update(function ($query) use ($request) {
    $caseSql = collect($request->options)->reduce(function ($sql, $name, $id) {
        return $sql . "WHEN id = {$id} THEN '{$name}' ";
    }, '');
    return $query->update(['name' => \DB::raw("{$caseSql} ELSE name END")]);
});

// 批量删除关联选项
$question->options()->whereIn('id', $request->deletedoptions)->delete();

// 批量新增关联选项
$newOptions = $question->options()->createMany(
    collect($request->newoptions)->map(fn($name) => ['name' => $name])->toArray()
);

5. 事务保证原子性（可选） #

若需确保增删改操作的一致性，将所有操作包裹在事务中，任一操作失败则回滚全部：

\DB::transaction(function () use ($request) {
    // 执行上述批量更新、删除、插入操作
});

内容的提问来源于stack exchange，提问作者Hitesh Vaghani