Laravel 如何能做到有效及优雅地进行批量UPDATE?

childs() 里可以做排序

创建模型基类，下面的 updateBatch 方法通过 case when 语句实现了一条语句批量更新多条记录

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model as EloquentModel;
use Illuminate\Support\Facades\DB;

/**
 * Model 基类
 *
 * @author brad <brader.wen@gmail.com>
 * @date 2022-03-02
 */
class Model extends EloquentModel
{
    /**
     * 创建时间
     */
    const CREATED_AT = 'created_at';

    /**
     * 更新时间
     */
    const UPDATED_AT = 'updated_at';

    /**
     * @var string 日期格式化
     */
    protected $dateFormat = 'U';

    /**
     * 时间字段返回格式【默认为Carbon对象】
     *
     * @var array
     */
    protected $casts = [
        'created_at' => 'timestamp',
        'updated_at' => 'timestamp',
    ];

    /**
     * @var array 不可操作字段
     */
    protected $guarded = [];

    /**
     * 存储
     *
     * @param array $args
     * @return Mixed
     */
    public function store(array $args)
    {
        if (empty($args)) {
            return false;
        }

        $model = new static($args);
        $model->save();

        return $model;
    }

    /**
     * 存储
     *
     * @param array $args
     * @return Mixed
     */
    protected function _insert(array $args)
    {
        if (empty($args)) {
            return false;
        }

        return DB::table($this->table)->insert($args);
    }

    /**
     * 批量更新
     *
     * @param array $inputs
     * @param string $where_field
     * @param string $when_field
     * @return mixed
     *
     * [['id' => 1, 'status' => 1], ['id' => 2, 'status' => 1]]
     *
     * update users set name =
     *    case
     *    when id = 1 then 'a'
     *    when id = 2 then 'b'
     * where id in (1,2);
     */
    public function updateBatch(array $inputs, $where_field = 'id', $when_field = 'id')
    {
        if (empty($inputs)) {
            throw new \InvalidArgumentException('The update data is empty.');
        }
        if (!($where = array_pluck($inputs, $where_field)) || !($when = array_pluck($inputs, $when_field))) {
            throw new \InvalidArgumentException('Missing update condition');
        }
        $when_arr = [];
        foreach ($inputs as $k => $input) {
            if (true == $this->timestamps && !is_null(static::UPDATED_AT)) {
                $input[static::UPDATED_AT] = time();
            }

            $when_val = $input[$when_field] ?? '';
            if (1 > strlen($when_val)) throw new \InvalidArgumentException('inputs when field must be require');
            foreach ($input as $key => $value) {
                if ($key == $when_field) continue;
                $when_arr[$key][] = "when {$when_field} = '{$when_val}' then '{$value}'";
            }
        }
        $build = DB::connection($this->getConnectionName())->table($this->getTable())->whereIn($where_field, $where);
        foreach ($when_arr as $key => &$item) {
            $item = DB::raw('case ' . implode(' ', $item) . ' end ');
        }
        return $build->update($when_arr);
    }

    /**
     * 批量创建
     *
     * @param array $inputs
     * @return bool
     */
    public function storeBatch(array $inputs)
    {
        if (true == $this->timestamps) {
            foreach ($inputs as $key => $input) {
                $time = time();
                if (!is_null(static::CREATED_AT)) {
                    $inputs[$key][static::CREATED_AT] = $time;
                }
                if (!is_null(static::UPDATED_AT)) {
                    $inputs[$key][static::UPDATED_AT] = $time;
                }
            }
        }
        $query = DB::connection($this->getConnectionName())->table($this->getTable());
        return $query->insert($inputs);
    }

    /**
     * Get the number of models to return per page.
     *
     * @return int
     */
    public function getPerPage()
    {
        return request('per_page', 20);
    }
}

mavinoo/laravel-batch

文档上upsert()的方法踩过了吗？

快速入门《Laravel 9 中文文档》