Laravel 5.3中的批量插入

Question

$list = [];

foreach($RoleDetails["Data"]["Permissions"] as $Permission) {
    $MyModel = new UserRolePermissionModel();
    $MyModel->UserID                  = $User->UserID;
    $MyModel->RolePermissionID        = $Permission->RolePermissionID;
    $MyModel->IsActive                = $Permission->IsActive;

    array_push($list, $MyModel);
}
\DB::table('tbluserrolepermission')->insert($list); 

以下是错误详细信息。

QueryException {#293 ▼
  #sql: "insert into `tbluserrolepermission` (`0`, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`, `13`, `14`, `15`, `16`, `17`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
  #bindings: array:18 [▶]
  #message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: insert into `tbluserrolepermission` (`0`, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`, `13`, `14`, `15`, `16`, `17`) values ({"UserID":21,"RolePermissionID":19,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":20,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":21,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":22,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":23,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":24,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":25,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":26,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":27,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":28,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":29,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":30,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":31,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":32,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":33,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":34,"IsActive":1,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":35,"IsActive":1,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":36,"IsActive":1,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}))"
  #code: "42S22"
  #file: "C:\xampp\htdocs\AS4\vendor\laravel\framework\src\Illuminate\Database\Connection.php"
  #line: 761
  -previous: PDOException {#356 ▶}
  +errorInfo: array:3 [▶]
  +"previous": PDOException {#356 ▶}
  -trace: {▶}
}

- Pankaj

---

里面有什么？ {#285 ▶} ？ - Punit Gajjar

Answer 1

这不起作用，因为您没有提供一个只包含与列名匹配的值的数组来填充数据库。

例子

假设您想填充一个用户数组并将其保存到数据库。以下示例可以实现此目的。

DB::table('users')->insert([  
    ['email' => '[e-mail]', 'votes' => 0],
    ['email' => '[e-mail]', 'votes' => 0]
]);

请注意，上面示例中的email和votes是users表中的2列。请注意，只有其他数组的数组被传递给插入方法。实际上，您要插入的是一组精美模型对象的数组。

来源：https://laravel.com/docs/5.3/queries#inserts

- henrik

Answer 2

我像下面这样修复了它。

foreach($RoleDetails["Data"]["RolePermissions"] as $RolePermission) {
    $data = [
        'UserID'                => $User->UserID,
        'RolePermissionID'      => $RolePermission->RolePermissionID,
        'IsActive'              => $RolePermission->IsActive,
        'IsProtectionAvailable' => $RolePermission->IsProtectionAvailable,
        'IsProtectionReadOnly'  => $RolePermission->IsProtectionReadOnly
    ];
    array_push($list,$data);
}
\DB::table('tbluserrolepermission')->insert($list); 

也可以是这样。

UserRolePermissionModel::insert($list);

- Pankaj

Answer 3

你能试试这个吗？

UserRolePermissionModel::insert($list->toArray());

OR 

DB::table('table')->insert($list->toArray()); 

- Ronak Dattani