Bulk Insertion in Laravel using eloquent ORM

Question

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.

Japanese

Do you have a has_many relationship on your models? – Bridgid

@jonathandey no i dont have any relationships at the moment – Japanese

@DavidBarker i have tried forming the quesr string using a for loop. I have also tried using transactions in laravel. – Japanese

@AramBhusal Could you post up your code? I'm sure I have some code here that will help you. – Disinfection

Have you seen laravel.com/docs/database/eloquent#mass-assignment? – Transferase

@DavidBarker I have tried this approach https://mcmap.net/q/119587/-php-bulk-insert-foreach and used pdo instance to prepare and execute satement. I am running into SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters – Japanese

@Transferase mass assignment is automatically assigning inputs to a single model's fields, it is not about creating multiple models at once. – Bobseine

Answer 1

You can just use Eloquent::insert().

For example:

$data = [
    ['name'=>'Coder 1', 'rep'=>'4096'],
    ['name'=>'Coder 2', 'rep'=>'2048'],
    //...
];

Coder::insert($data);

Renfrew

Does this still apply to Laravel 4? – Varini

@advait: yes, it still applies to Laravel 4. – Lilylilyan

@Lilylilyan where is the insert method located? I can't seem to find it in the Model access class: laravel.com/api/class-Illuminate.Database.Eloquent.Model.html – Varini

@Varini sorry, not been here in a while. check laravel.com/api/class-Illuminate.Database.Query.Builder.html – Lilylilyan

Worth noting that it doesn't touch Eloquent in fact. It just proxies the call to Query\Builder@insert() method. There's no way to efficiently insert multiple rows with Eloquent, nor does it offer any method for bulk inserts. – Startle

Please note that if you use i.e. Model::insert(array($model1->toArray(), ...)), that ->toArray() won't present you with a flat array of attributes if your model has relations or casts. You could look into ->getArrayableAttributes(), but you don't get the timezones... In short; there's no nice way of doing this. – Diachronic

How to save if I have two foreign keys in one table and I want multi insert, it should insert foreign ids as well automatically using Eloquent, I'm using Laravel 5. – Subcelestial

@CanVural what should we do to update/create timestamps also? – Tildy

How can we get all the IDs of newly inserted rows? – Davao

@Renfrew what if there occur a problem at a starting record, In this case records after this problematic record will be inserted into db? – Rebekahrebekkah

This will use one insert. So, given a big enough array, it will fail. – Danu

@JarekTkaczyk is right, and that makes the model based call a misleading abstraction because it bypasses things like $fillable and the model events such as creating, created... It all gets skipped. – Thoracotomy

Yes, you need to be careful with this one. – Derwood

@MilanMaharjan you can simply set the created_at, updated_at fields manually in the array you pass to Model::insert() – Feldman

it does adds values to created_at and updated_at i have tried and verified. – Frigate

@SagarAhuja I just tried the same, it does not insert "created_at" and "updated_at" values automatically - maybe you set them manually. – Finnie

How can I get inserted IDs using this? – Napiform

still applies to laravel 9. inserts what would be over 45 seconds for 4000 rows in 1 second. your mileage of course will vary. – Spada

I appreciate your advice, and while it works great with only the insert operation, it would be helpful if someone could advise me as to if there is a way to update or insert data using a batch array. – Primary

Answer 2

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);

Rheumy

Or use Carbon in the beginning of the script to define a $now variable: $now = Carbon::now('utc')->toDateTimeString();. Then just use 'created_at' => $now, 'updated_at' => $now for every insertion. – Supranational

How can we get all the IDs of newly inserted rows? – Davao

Why 'utc'? Is it project's preferency, or, does eloquent always work in 'utc'? – Jamima

Do not use 'utc' if you want to use your server's datetime, otherwise you'll be using the 'Coordinated Universal Time'. In most of the cases you'll want to save the data in your local time. Then use Carbon::now()->toDateTimeString() – Carolyncarolyne

I don't want to start a huge "spaces vs. tabs" argument, but please, save timestamps in UTC! It will save you a huge amount of pain later on! Think about users globally :) – Kith

If I may ask, what's the big need for Carbon in this situation? What's wrong with date("Y-m-d H:i:s")? – Palatial

@IfediOkonkwo they use it to handle conversion to UTC instead of local time() and manual calculation. – Finnie

@Kith Not only globally -- just think about your users in other cities and states! – Bobseine

Was missing use Illuminate\Support\Carbon; at the top of the file. – Trusting

Answer 3

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);

Adhesion

Answer 4

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;
}

Derwood

This is not what's called bulk insert. Because of the poor implementation, this function will prepare and execute the same query once per Item. – Agential

It's worth noting this is a relationship method, can cannot be called directly from the model i.e. Model::createMany(). – Byrnes

Answer 5

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],
    ..................................
]);

Crucible

Answer 6

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

Flofloat

Answer 7

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));

Hannie

Answer 8

$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".

Unheardof

Answer 9

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);
    }
}

Aria

This makes one query per item. It's not a bulk insertion. – Psf

@EmileBergeron I agree with you. I have edited my post so maybe this could help to have good bulk inserting. Considering leaving the tasks that take a lot of time out of the loop (carbon, bcrypt) this can save you a lot of time. – Aria

@FranciscoDaniel I appreciate your advice, and while it works great with only the insert operation, it would be helpful if someone could advise me as to if there is a way to update or insert data using a batch array. – Primary

Answer 10

You can Create on by one and get return collection of created instance of model

  $createdCollection= collect([
            ['name' => 'América'],
            ['name' => 'África'],
            ['name' => 'Europa'],
            ['name' => 'Asia'],
            ['name' => 'Oceanía'],
        ])->map(function ($item, $key) {
            return Continent::create($item);
        });

Strainer

I think this isn't bulk insert – Empurple

Answer 11

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)

Yah

Answer 12

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();
});

Closure