我想在Laravel中批量更新记录,但是记录没有得到更新。对于每个ID,我有一个不同的记录。以下是我的尝试:
$ids = [5,6,8,9],
$updated_array = [
['name' => 'tarun'],
['name' => 'Akash'],
['name' => 'Soniya'],
['name' => 'Shalu'],
];
Model::whereIn('id', $ids)->update($updated_array);
当您尝试将多行更新为相同值时,可以使用批量更新。您无法使用不同的值进行批量更新。
因此,以下操作将起作用,但会将所有匹配的记录更新为名称“tarun”:
Model::whereIn('id', $ids)->update(['name' => 'tarun']);
对于您的示例,您可以执行以下操作:
foreach($ids as $key => $id) {
Model::where('id', $id)->update($updated_array[$key]);
}
但据我所知,如果不在Laravel中运行4个查询并编写一条原始的SQL语句来完成此操作,就没有其他方法可以实现。
User::query()->upsert([
['id' => 1, 'email' => 'dadan@example.com'],
['id' => 2, 'email' => 'satria@example.com'],
], 'email');
这个功能仅适用于 Laravel 8 或更新的版本
这个问题的一些好的解决方案在以下帖子中: https://github.com/laravel/ideas/issues/575
1)可以按照barryvdh在帖子中的评论所述,创建使用原始SQL的自定义函数来解决此问题。
public static function updateValues(array $values)
{
$table = MyModel::getModel()->getTable();
$cases = [];
$ids = [];
$params = [];
foreach ($values as $id => $value) {
$id = (int) $id;
$cases[] = "WHEN {$id} then ?";
$params[] = $value;
$ids[] = $id;
}
$ids = implode(',', $ids);
$cases = implode(' ', $cases);
$params[] = Carbon::now();
return \DB::update("UPDATE `{$table}` SET `value` = CASE `id` {$cases} END, `updated_at` = ? WHERE `id` in ({$ids})", $params);
}
这显然可以将性能提高13倍。
2)根据另一条评论,另一个想法是按每个记录的基础进行操作,但整个过程作为单个事务完成,这样速度会更快。
DB::beginTransaction();
// your loop and updates;
if( !$user )
{
rollbackTransaction();
} else {
// Else commit the queries
commitTransaction();
}
3)还有一个Laravel库,似乎也试图解决这个问题。https://github.com/mavinoo/laravelBatch
注意:我没有尝试或测试上述任何解决方案。
解决方案 #1. 首先,您可以使用原生的Laravel Upsert进行批量更新:
$payload = [
['id' => 1, 'value' => 'foo'],
['id' => 2, 'value' => 'bar'],
];
Setting::upsert($payload, ['id'], ['value']);
upsert
创建了SQL查询:INSERT ON DUPLICATE KEY UPDATE。
这是引擎的机制:在插入之前,会搜索并更新计数器中的下一个ID。然后尝试将其插入到表中,然后更新它,但是ID计数器不会回滚。这就是为什么这种机制适用于主键而不是数字或没有自动递增的原因。
关于这个问题的更多细节:ON DUPLICATE KEY + AUTO INCREMENT issue mysql
解决方案#2。为Illuminate\Database\Eloquent\Builder创建Laravel宏,仅用于批量更新
使用示例:
$payload = [
['id' => 1, 'value' => 'foo'],
['id' => 2, 'value' => 'bar'],
];
Setting::bulkUpdate($payload, ['id'], ['value']);
UPDATE `settings`
SET `value` =
(CASE
WHEN `id` = '1' THEN 'foo'
WHEN `id` = '2' THEN 'bar'
END),
`description` =
(CASE
WHEN `id` = '1' THEN 'wow'
WHEN `id` = '2' THEN 'cool'
END)
WHERE `id` IN ('1', '2');
<?php
namespace App\Providers;
use Illuminate\Support\ServiceProvider;
use Illuminate\Database\Eloquent\Builder;
use App\Macros\BulkUpdateMacros;
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
Builder::macro('bulkUpdate', function(array $values, $uniqueBy, array $updates) {
return (new BulkUpdateMacros($this, $values, $uniqueBy, $updates))->handle();
});
}
}
<?php
namespace App\Macros;
use Illuminate\Database\Eloquent\Builder;
class BulkUpdateMacros {
/**
* The base query builder instance.
*
* @var \Illuminate\Database\Eloquent\Builder
*/
protected $builder;
/**
* Set of rows.
*
* @var array
*/
protected $values;
/**
* Identify fields.
*
* @var string|array
*/
protected $uniqueBy;
/**
* Create a new macros instance.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param array $values
* @param array $updates
* @return void
*/
public function __construct($builder, array $values, $uniqueBy, array $updates)
{
$this->builder = $builder;
$this->values = $values;
$this->updates = $updates;
$this->uniqueBy = $uniqueBy;
}
/**
* Compile raw sql query.
*
* @return string [sql query]
*/
private function getSqlQuery(): string
{
$values = $this->values;
$updates = $this->updates;
$uniqueBy = $this->uniqueBy;
if (!is_array(reset($values))) {
$values = [$values];
}
if (!is_array($uniqueBy)) {
$uniqueBy = [$uniqueBy];
}
$tableName = $this->builder->getModel()->getTable();
// compile columns for raw sql
$columns = collect($updates)->map(function($field) use ($values, $uniqueBy) {
$cases = collect($values)->map(function($row) use ($field, $uniqueBy) {
$conditions = collect($uniqueBy)->map(function($index) use ($row) {
return "`$index` = '$row[$index]'";
})->implode(' AND ');
$value = $row[$field];
return "WHEN $conditions THEN '$value'";
})->implode(' ');
return "`$field` = (CASE $cases END)";
})->implode(' ,');
// compile where clause
$where = collect($uniqueBy)->map(function($clause) use ($values) {
$values = collect($values)->map(function($row) use ($clause) {
return $row[$clause];
})->implode("', '");
return "`$clause` IN ('$values')";
})->implode(' AND ');
return "UPDATE `{$tableName}` SET {$columns} WHERE {$where}";
}
/**
* Execute query for bulk update rows.
*
* @return int [total count of affected rows]
*/
public function handle(): int
{
if (empty($this->values)) {
return 0;
}
$connection = $this->builder->getQuery()->getConnection();
return $connection->update($this->getSqlQuery());
}
}
$data_to_be_updated = [ ['id'=>5,'name' => 'tarun'], ['id'=>6, 'name' => 'Akash'],
['id'=>8, 'name' => 'Soniya'], ['id'=>9,'name' => 'Shalu']
];
foreach ($data_to_be_updated as $key => $value) {
$data = Model::where('id',$value['id'])->first();
if ($data) {
$data->name = $value['name'];
$data->save();
}
}
你可以像上面提到的那样做:
foreach($ids as $key => $id) {
Model::where('id', $id)->update($updated_array[$key]);
}
protected $guarded = array();
$fillable
和$guarded
属性用于启用批量插入和更新。 - lessan