Laravel 插入或更新多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40863015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel insert or update multiple rows
提问by Mateusz Kudej
Im new in laravel, and im trying to update my navigation tree. So i want to update my whole tree in one query without foreach.
我是 Laravel 的新手，我正在尝试更新我的导航树。所以我想在没有 foreach 的情况下在一个查询中更新我的整个树。
array(
array('id'=>1, 'name'=>'some navigation point', 'parent'='0'),
array('id'=>2, 'name'=>'some navigation point', 'parent'='1'),
array('id'=>3, 'name'=>'some navigation point', 'parent'='1')
);
I just want to ask - is there posibility in laravel to insert(if new in array) or update my current rows in database?
我只是想问 - laravel 中是否有可能插入（如果是新的数组）或更新我在数据库中的当前行？
I want to update all, because i have fields _lft, _right, parent_id in my tree and im using some dragable js plugin to set my navigation structure - and now i want to save it.
我想全部更新，因为我的树中有字段 _lft、_right、parent_id，并且我使用一些可拖动的 js 插件来设置我的导航结构 - 现在我想保存它。
I tried to use
我试着用
Navigation::updateOrCreate(array(array('id' => '3'), array('id'=>'4')), array(array('name' => 'test11'), array('name' => 'test22')));
But it works just for single row, not multiple like i tried to do. Maybe there is another way to do it?
但它只适用于单行，而不是像我试图做的多行。也许有另一种方法可以做到？
回答by Oluwatobi Samuel Omisakin
I wonder why this kind of feature is not yet available in Laravel core (till today). Check out this gistThe result of the query string would look like this: here
我想知道为什么 Laravel 核心中还没有这种功能（直到今天）。查看这个要点查询字符串的结果将如下所示：here
I am putting the code here just in case the link breaks in the future, I am not the author:
我把代码放在这里以防将来链接断开，我不是作者：
/**
* Mass (bulk) insert or update on duplicate for Laravel 4/5
*
* insertOrUpdate([
* ['id'=>1,'value'=>10],
* ['id'=>2,'value'=>60]
* ]);
*
*
* @param array $rows
*/
function insertOrUpdate(array $rows){
$table = \DB::getTablePrefix().with(new self)->getTable();
$first = reset($rows);
$columns = implode( ',',
array_map( function( $value ) { return "$value"; } , array_keys($first) )
);
$values = implode( ',', array_map( function( $row ) {
return '('.implode( ',',
array_map( function( $value ) { return '"'.str_replace('"', '""', $value).'"'; } , $row )
).')';
} , $rows )
);
$updates = implode( ',',
array_map( function( $value ) { return "$value = VALUES($value)"; } , array_keys($first) )
);
$sql = "INSERT INTO {$table}({$columns}) VALUES {$values} ON DUPLICATE KEY UPDATE {$updates}";
return \DB::statement( $sql );
}
So you can safely have your arrays inserted or updated as:
因此，您可以安全地将数组插入或更新为：
insertOrUpdate(
array(
array('id'=>1, 'name'=>'some navigation point', 'parent'='0'),
array('id'=>2, 'name'=>'some navigation point', 'parent'='1'),
array('id'=>3, 'name'=>'some navigation point', 'parent'='1')
)
);
Just in case any trouble with the first line in the function you can simply add a table name as a second argument, then comment out the line i.e:
以防万一函数中的第一行出现任何问题，您可以简单地添加一个表名作为第二个参数，然后注释掉该行，即：
function insertOrUpdate(array $rows, $table){
.....
}
insertOrUpdate(myarrays,'MyTableName');
NB: Be careful though to sanitise your input! and remember the timestamp fields are not touched. you can do that by adding manually to each arrays in the main array.
注意：尽管清理您的输入要小心！并记住时间戳字段没有被触及。您可以通过手动添加到主数组中的每个数组来实现。
回答by Xebio
Eloquent Style
雄辩的风格
public function meta(){ // in parent models.
return $this->hasMany('App\Models\DB_CHILD', 'fk_id','local_fk_id');
}
. . .
. . .
$parent= PARENT_DB::findOrFail($id);
$metaData= [];
foreach ($meta['meta'] as $metaKey => $metaValue) {
if ($parent->meta()->where([['meta_key', '=',$metaKey]] )->exists()) {
$parent->meta()->where([['meta_key', '=',$metaKey]])->update(['meta_value' => $metaValue]);
}else{
$metaData[] = [
'FK_ID'=>$fkId,
'meta_key'=>$metaKey,
'meta_value'=> $metaValue
];
}
}
$Member->meta()->insert($metaData);
回答by Alexey Mezenin
No, you can't do this. You can insert()multiple rows at once and you can update()multiple rows using same where()condition, but if you want to use updateOrCreate(), you'll need to use foreach()loop.
不，你不能这样做。您可以insert()一次多行，也可以update()使用相同where()条件的多行，但如果要使用updateOrCreate()，则需要使用foreach()循环。
回答by Borna
in your controller use DB; public function arrDta(){
在您的控制器中使用 DB；公共函数 arrDta(){
$up_or_create_data=array(
array('id'=>2, 'name'=>'test11'),
array('id'=>4, 'name'=>'test22')
);
var_dump($up_or_create_data);
echo "fjsdhg";
foreach ($up_or_create_data as $key => $value) {
echo "key ".$key;
echo "<br>";
echo " id: ".$up_or_create_data[$key]["id"];
echo "<br>";
echo " Name: ".$up_or_create_data[$key]["name"];
if (Navigation::where('id', '=',$up_or_create_data[$key]["id"])->exists()) {
DB::table('your_table_ name')->where('id',$up_or_create_data[$key]["id"])->update(['name' => $up_or_create_data[$key]["name"]]);
}else{
DB::insert('insert into your_table_name (id, name) values (?, ?)', [$up_or_create_data[$key]["id"], $up_or_create_data[$key]["name"]]);
}
}
回答by Jonas Staudenmeir
I've created an UPSERT package for all databases: https://github.com/staudenmeir/laravel-upsert
我为所有数据库创建了一个 UPSERT 包：https: //github.com/staudenmeir/laravel-upsert
DB::table('navigation')->upsert(
[
['id' => 1, 'name' => 'some navigation point', 'parent' => '0'],
['id' => 2, 'name' => 'some navigation point', 'parent' => '1'],
['id' => 3, 'name' => 'some navigation point', 'parent' => '1'],
],
'id'
);
