laravel5.5 批量更新

laravel5.5 批量更新 单文件

“mavinoo/laravel-batch”: “^2.3” 基于以上扩展代码的抽出来的代码
因为我使用的项目不支持使用该扩展，使用需要修改框架源码的 getDateFormat。

1. 在项目根目录下 app/Traits/ModelTraits.php
2. 将以下代码粘贴进入 ModelTraits.php

<?php

namespace App\Traits;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

trait ModelTraits
{
       /**
     * <h2>Update multiple rows.</h2>
     *
     * Example:<br>
     * ```
     * $userInstance = new \App\Models\User;
     * $value = [
     *     [
     *         'id' => 1,
     *         'status' => 'active',
     *         'nickname' => 'Mohammad'
     *     ],
     *     [
     *         'id' => 5,
     *         'status' => 'deactive',
     *         'nickname' => 'Ghanbari'
     *     ],
     *     [
     *         'id' => 7,
     *         'balance' => ['+', 500]
     *     ]
     * ];
     * $index = 'id';
     * Model::updateBatch($userInstance, $value, $index);
     * ```
     *
     * @param \Illuminate\Database\Eloquent\Model $this
     * @param array $values
     * @param string $index
     * @param bool $raw
     * @return bool|int
     */
    public static function updateBatch(array $values, string $index = null, bool $raw = false)
    {
        $object = new self();

        $final = [];
        $ids = [];

        if (!count($values)) {
            return false;
        }

        if (!isset($index) || empty($index)) {
            $index = $object->getKeyName();
        }

        $connection = config('database.default');
        $driver = config("database.connections.{$connection}.driver");

        foreach ($values as $key => $val) {
            $ids[] = $val[$index];

            if ($object->usesTimestamps()) {
                $updatedAtColumn = $object->getUpdatedAtColumn();

                if (!isset($val[$updatedAtColumn])) {
                    $val[$updatedAtColumn] = Carbon::now()->format($object->getDateFormat());
                }
            }

            foreach (array_keys($val) as $field) {
                if ($field !== $index) {
                    // If increment / decrement
                    if (gettype($val[$field]) == 'array') {
                        // If array has two values
                        if (!array_key_exists(0, $val[$field]) || !array_key_exists(1, $val[$field])) {
                            throw new \ArgumentCountError('Increment/Decrement array needs to have 2 values, a math operator (+, -, *, /, %) and a number');
                        }
                        // Check first value
                        if (gettype($val[$field][0]) != 'string' || !in_array($val[$field][0], ['+', '-', '*', '/', '%'])) {
                            throw new \TypeError('First value in Increment/Decrement array needs to be a string and a math operator (+, -, *, /, %)');
                        }
                        // Check second value
                        if (!is_numeric($val[$field][1])) {
                            throw new \TypeError('Second value in Increment/Decrement array needs to be numeric');
                        }
                        // Increment / decrement
                        if (self::disableBacktick($driver)) {
                            $value = $field . $val[$field][0] . $val[$field][1];
                        } else {
                            $value = '`' . $field . '`' . $val[$field][0] . $val[$field][1];
                        }
                    } else {
                        // Only update
                        $finalField = $raw ? self::mysql_escape($val[$field]) : "'" . self::mysql_escape($val[$field]) . "'";
                        $value = (is_null($val[$field]) ? 'NULL' : $finalField);
                    }

                    if (self::disableBacktick($driver))
                        $final[$field][] = 'WHEN ' . $index . ' = \'' . $val[$index] . '\' THEN ' . $value . ' ';
                    else
                        $final[$field][] = 'WHEN `' . $index . '` = \'' . $val[$index] . '\' THEN ' . $value . ' ';
                }
            }
        }

        if (self::disableBacktick($driver)) {

            $cases = '';
            foreach ($final as $k => $v) {
                $cases .= '"' . $k . '" = (CASE ' . implode("\n", $v) . "\n"
                    . 'ELSE "' . $k . '" END), ';
            }

            $query = "UPDATE \"" . $object->getFullTableName($object) . '" SET ' . substr($cases, 0, -2) . " WHERE \"$index\" IN('" . implode("','", $ids) . "');";

        } else {

            $cases = '';
            foreach ($final as $k => $v) {
                $cases .= '`' . $k . '` = (CASE ' . implode("\n", $v) . "\n"
                    . 'ELSE `' . $k . '` END), ';
            }

            $query = "UPDATE `" . $object->getFullTableName($object) . "` SET " . substr($cases, 0, -2) . " WHERE `$index` IN(" . '"' . implode('","', $ids) . '"' . ");";

        }

        return DB::connection($object->getConnectionName())->update($query);
    }
    /**
     * Disable Backtick.
     *
     * @param $drive
     * @return boolean
     */
    public static function disableBacktick($drive)
    {
        return in_array($drive, ['pgsql', 'sqlsrv']);
    }

    /**
     * Escape values according to mysql.
     *
     * @param $fieldValue
     * @return array|string|string[]
     */
    public static function mysql_escape($fieldValue)
    {
        if (is_array($fieldValue)) {
            return array_map(__METHOD__, $fieldValue);
        }

        if (is_bool($fieldValue)) {
            return (int)$fieldValue;
        }

        if (self::is_json($fieldValue)) {
            return self::safeJson($fieldValue);
        }

        if (!empty($fieldValue) && is_string($fieldValue)) {
            return str_replace(
                ['\\', "\0", "\n", "\r", "'", '"', "\x1a"],
                ['\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'],
                $fieldValue
            );
        }

        return $fieldValue;
    }

    protected static function is_json($str): bool
    {
        if (!is_string($str)  || is_numeric($str)) {
            return false;
        }
        $json = json_decode($str);
        return $json && $str != $json;
    }

    protected static function safeJson($jsonData, $asArray = false)
    {
        $jsonData = json_decode($jsonData, true);
        $safeJsonData = [];
        if (!is_array($jsonData)) {
            return $jsonData;
        }
        foreach ($jsonData as $key => $value) {
            if (self::is_json($value)) {
                $safeJsonData[$key] = self::safeJson($value, true);
            } elseif (is_string($value)) {
                $safeJsonData[$key] = self::safeJsonString($value);
            } elseif (is_array($value)) {
                $safeJsonData[$key] = self::safeJson(json_encode($value), true);
            } else {
                $safeJsonData[$key] = $value;
            }
        }
        return $asArray ? $safeJsonData : json_encode($safeJsonData, JSON_UNESCAPED_UNICODE);
    }

    protected static function safeJsonString($fieldValue)
    {
        return str_replace(
            ["'"],
            ["''"],
            $fieldValue
        );
    }

    /**
     * Get full table name.
     *
     * @param Model $model
     * @return string
     */
    private function getFullTableName(Model $model)
    {
        return $model->getConnection()->getTablePrefix() . $model->getTable();
    }
}

3. 在模型中 use ModelTraits;

<?php

namespace App\Models;

use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
use App\Traits\ModelTraits;

class User extends Authenticatable
{
	use ModelTraits;
}

4. 使用

	$value = [
	    [
	        'id' => 1,
	        'status' => 'active',
	        'nickname' => 'Mohammad'
	    ],
	    [
	        'id' => 5,
	        'status' => 'deactive',
	        'nickname' => 'Ghanbari'
	    ],
	    [
	        'id' => 7,
	        'balance' => ['+', 500]
	    ]
	];
	$index = 'id';
	\App\Models\User::updateBatch($value, $index);