Bulk Insertion in Laravel using eloquent ORM
PhpLaravelEloquentPhp Problem Overview
How can we perform bulk database insertions in Laravel using Eloquent ORM?
I am working with an XML document, looping through its elements. I want to accomplish something like this in Laravel:
$sXML = download_page('http://remotepage.php&function=getItems&count=100&page=1');
$oXML = new SimpleXMLElement($sXML);
$query = "INSERT INTO tbl_item (first_name, last_name, date_added) VALUES";
foreach($oXML->results->item->item as $oEntry){
$query .= "('" . $oEntry->firstname . "', '" . $oEntry->lastname . "', '" . date("Y-m-d H:i:s") . "'),";
}
mysql_query($query);
but I am getting the following error.
> SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters.
Php Solutions
Solution 1 - Php
You can just use Eloquent::insert().
For example:
$data = [ ['name'=>'Coder 1', 'rep'=>'4096'],
['name'=>'Coder 2', 'rep'=>'2048'],
//...
];
Coder::insert($data);
Solution 2 - Php
We can update GTF answer to update timestamps easily
$data = array(
array(
'name'=>'Coder 1', 'rep'=>'4096',
'created_at'=>date('Y-m-d H:i:s'),
'modified_at'=> date('Y-m-d H:i:s')
),
array(
'name'=>'Coder 2', 'rep'=>'2048',
'created_at'=>date('Y-m-d H:i:s'),
'modified_at'=> date('Y-m-d H:i:s')
),
//...
);
Coder::insert($data);
Update: to simplify the date we can use carbon as @Pedro Moreira suggested
$now = Carbon::now('utc')->toDateTimeString();
$data = array(
array(
'name'=>'Coder 1', 'rep'=>'4096',
'created_at'=> $now,
'modified_at'=> $now
),
array(
'name'=>'Coder 2', 'rep'=>'2048',
'created_at'=> $now,
'modified_at'=> $now
),
//...
);
Coder::insert($data);
UPDATE2: for laravel 5 , use updated_at instead of modified_at
$now = Carbon::now('utc')->toDateTimeString();
$data = array(
array(
'name'=>'Coder 1', 'rep'=>'4096',
'created_at'=> $now,
'updated_at'=> $now
),
array(
'name'=>'Coder 2', 'rep'=>'2048',
'created_at'=> $now,
'updated_at'=> $now
),
//...
);
Coder::insert($data);
Solution 3 - Php
This is how you do it in more Eloquent way,
$allinterests = [];
foreach($interests as $item){ // $interests array contains input data
$interestcat = new User_Category();
$interestcat->memberid = $item->memberid;
$interestcat->catid = $item->catid;
$allinterests[] = $interestcat->attributesToArray();
}
User_Category::insert($allinterests);
Solution 4 - Php
To whoever is reading this, check out createMany() method.
/**
* Create a Collection of new instances of the related model.
*
* @param array $records
* @return \Illuminate\Database\Eloquent\Collection
*/
public function createMany(array $records)
{
$instances = $this->related->newCollection();
foreach ($records as $record) {
$instances->push($this->create($record));
}
return $instances;
}
Solution 5 - Php
I searched many times for it, finally used custom timestamps like below:
$now = Carbon::now()->toDateTimeString();
Model::insert([
['name'=>'Foo', 'created_at'=>$now, 'updated_at'=>$now],
['name'=>'Bar', 'created_at'=>$now, 'updated_at'=>$now],
['name'=>'Baz', 'created_at'=>$now, 'updated_at'=>$now],
..................................
]);
Solution 6 - Php
Eloquent::insert is the proper solution but it wont update the timestamps, so you can do something like below
$json_array=array_map(function ($a) {
return array_merge($a,['created_at'=>
Carbon::now(),'updated_at'=> Carbon::now()]
);
}, $json_array);
Model::insert($json_array);
The idea is to add created_at and updated_at on whole array before doing insert
Solution 7 - Php
$start_date = date('Y-m-d h:m:s');
$end_date = date('Y-m-d h:m:s', strtotime($start_date . "+".$userSubscription['duration']." months") );
$user_subscription_array = array(
array(
'user_id' => $request->input('user_id'),
'user_subscription_plan_id' => $request->input('subscription_plan_id'),
'name' => $userSubscription['name'],
'description' => $userSubscription['description'],
'duration' => $userSubscription['duration'],
'start_datetime' => $start_date,
'end_datetime' => $end_date,
'amount' => $userSubscription['amount'],
'invoice_id' => '',
'transection_datetime' => '',
'created_by' => '1',
'status_id' => '1', ),
array(
'user_id' => $request->input('user_id'),
'user_subscription_plan_id' => $request->input('subscription_plan_id'),
'name' => $userSubscription['name'],
'description' => $userSubscription['description'],
'duration' => $userSubscription['duration'],
'start_datetime' => $start_date,
'end_datetime' => $end_date,
'amount' => $userSubscription['amount'],
'invoice_id' => '',
'transection_datetime' => '',
'created_by' => '1',
'status_id' => '1', )
);
dd(UserSubscription::insert($user_subscription_array));
UserSubscription is my model name.
This will return "true" if insert successfully else "false".
Solution 8 - Php
Maybe a more Laravel way to solve this problem is to use a collection and loop it inserting with the model taking advantage of the timestamps.
<?php
use App\Continent;
use Illuminate\Database\Seeder;
class InitialSeeder extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
collect([
['name' => 'América'],
['name' => 'África'],
['name' => 'Europa'],
['name' => 'Asia'],
['name' => 'Oceanía'],
])->each(function ($item, $key) {
Continent::forceCreate($item);
});
}
}
EDIT:
Sorry for my misunderstanding. For bulk inserting this could help and maybe with this you can make good seeders and optimize them a bit.
<?php
use App\Continent;
use Carbon\Carbon;
use Illuminate\Database\Seeder;
class InitialSeeder extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
$timestamp = Carbon::now();
$password = bcrypt('secret');
$continents = [
[
'name' => 'América'
'password' => $password,
'created_at' => $timestamp,
'updated_at' => $timestamp,
],
[
'name' => 'África'
'password' => $password,
'created_at' => $timestamp,
'updated_at' => $timestamp,
],
[
'name' => 'Europa'
'password' => $password,
'created_at' => $timestamp,
'updated_at' => $timestamp,
],
[
'name' => 'Asia'
'password' => $password,
'created_at' => $timestamp,
'updated_at' => $timestamp,
],
[
'name' => 'Oceanía'
'password' => $password,
'created_at' => $timestamp,
'updated_at' => $timestamp,
],
];
Continent::insert($continents);
}
}
Solution 9 - Php
From Laravel 5.7 with Illuminate\Database\Query\Builder you can use insertUsing method.
$query = [];
foreach($oXML->results->item->item as $oEntry){
$date = date("Y-m-d H:i:s")
$query[] = "('{$oEntry->firstname}', '{$oEntry->lastname}', '{$date}')";
}
Builder::insertUsing(['first_name', 'last_name', 'date_added'], implode(', ', $query));
Solution 10 - Php
For category relations insertion I came across the same problem and had no idea, except that in my eloquent model I used Self() to have an instance of the same class in foreach to record multiple saves and grabing ids.
foreach($arCategories as $v)
{
if($v>0){
$obj = new Self(); // this is to have new instance of own
$obj->page_id = $page_id;
$obj->category_id = $v;
$obj->save();
}
}
without "$obj = new Self()" it only saves single record (when $obj was $this)
Solution 11 - Php
Problem solved... Alter table for migrate
$table->timestamp('created_at')->nullable()->useCurrent();
Solution:
Schema::create('spider_news', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('source')->nullable();
$table->string('title')->nullable();
$table->string('description')->nullable();
$table->string('daterss')->nullable();
$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->useCurrent();
});