CODEWITHSUNDEEP
phpmysqllaravellaravel-4eloquent
Mahmoud Zalt
Mahmoud Zalt

Reputation: 31100

How to save multiple records with one query in Laravel?

I have over 100,000 records in my hand, as Array of STD Objects (can be converted to Array of Arrays or Array of Eloquent Models).

How to store all of them in the database with 1 query.

If you are wondering I got these data from another database connection to save them into my database.

Of course I can build each Eloquent model and call the save() function but this will take a lot of time and is not recommended anyway. I tried the insert() on the model passing to it an Array of Eloquent Models, but didn't worked for some reasons!!

So what options do I have, other than generating the raw SQL query?

I am using Laravel 4.2 here.

Upvotes: 3

Views: 6575

Answers (1)

patricus
patricus

Reputation: 62228

You can use the insert() method on the Model, but it must be an array of arrays. You also need to make sure that all your array entries have the same keys. If an entry is missing a key, the inserted values for that entry may be off.

You mentioned you're starting with an array of objects. You can convert this array of objects to an array of arrays, and then use the insert statement.

Assuming you have a Book model:

$data = [
    (object) [
        'title' => 'Title 1',
        'author' => 'Author 1',
        'publisher' => 'Publisher 1',
    ],
    (object) [
        'title' => 'Title 2',
        'author' => 'Author 2',
        'publisher' => null, // make sure key exists; use null value;
    ],
];

// Convert your objects to arrays. You can use array_walk instead,
// if you prefer to just modify the original $data
$arrayData = array_map(function($value) {
    return (array) $value;
}, $data);

// do the insert
$success = Book::insert($arrayData);

// view last query run (to confirm one bulk insert statement)
$log = DB::getQueryLog();
print_r(end($log));

I don't know the details about your existing data, so you may need some more complicated logic to ensure the arrays are correct, but the above is a gist of what should work.

Edit

When doing a bulk insert like this, you also need to pay attention to the max_allowed_packet MySQL variable. The default value for this variable is 1 MB, so if you generate a SQL statement larger than 1 MB, you will get an error. max_allowed_packet documentation

So, if this is your issue, you can either up the max_allowed_packet MySQL variable so that it is large enough to contain your entire SQL statement, or you can break your insert into chunks small enough to fit under the current max_allowed_packet size (default 1 MB).

An example of chunking your inserts:

// assume $arrayData is your array of arrays

// you can come up with some fancy algorithm to determine your optimal
// chunk size if you want. 
$size = 100;

$chunks = array_chunk($arrayData, $size);
foreach($chunks as $chunk) {
    Book::insert($chunk);
}

Upvotes: 4

Related Questions