Advanced Eloquent - Bulk Insert

Using Laravel's Eloquent eloquently

When inserting records according to some criteria, the most obvious way is to get a list, and iterate through, inserting along the way. However this has performance issues, and could even cause errors with RAM exhaustion, when working with large amounts of data.

Following is a cut-down example, done the usual way, and then a more "eloquent" use of Eloquent.

For this example I'll assume a fairly standard User model class, and another model called "Record" that will be the table holding the records we want to add. In the database, the record table looks like this:

record {
    id:        integer, auto increment,
    user_id:   integer,
    somedata:  varchar(255)

The obvious way

This is equivalent of running several separate 'INSERT' statements on the database. It might be ok if there are only a few, but it falls down once the numbers of records get into the 100s or 1000s. Here's the code:

use Models\User;
use Models\Record;

function dodgy() {
  $users = User::where('email','like','')->get();

  foreach ($users as $user) {
    $new = Record;
    $new->user_id = $user->id;
    $new->somedata = 'User is from';

Database admin's out there are shaking their heads in disgust. :-P

It might work, as long as you don't run out of RAM with the call to get(), however, even if you don't, it is horribly inefficient.

So, what do we do?

A better way

In this case all the data is already on the database server, so we can take advantage of that. You may be familiar with the form of INSERT that looks like this:

INSERT (col, col2, ...) INTO tablename SELECT col, col2, ... FROM tablename WHERE....

And here is how to get the Eloquent query builder to help us build that query.

use Models\User;

function better() {
  $users = User::select('user_id', DB:raw('User is from'))
               ->where('email','like',''); // No get()!

  $sql = 'INSERT ( user_id, somedata ) INTO record ' . $user->toSql();
  $bindings = $users->getBindings();

  $success = DB::statement( $sql, $bindings );

  return $success; // boolean

So what is happening here? First we do not call get(). We don't actually want to retrieve the results of the query, we only want to build the query SQL.

Now toSql() is called to get the built SQL statement. Note that two columns have been given with select that match what we want to insert.

The SQL is most likely not enough on it's own. We also need the values that will be bound to those '?'s you'll see in the SQL should you inspect it.

The INSERT code is prepended and then we call the statement to run the resulting SQL.

So there we have it. :-) In my testing, with around 5000 inserts, the second version was a matter of milliseconds, compared to minutes.

Even when loading data up to the database server, you can build a INSERT INTO table VALUES statement with multiple rows of values, which will be much much quicker than inserting records one by one, although there is an Array style insert with Eloquent that may work for you.

I've used this with Laravel 5.0, however it may work on older versions. It should work with newer with little or no changes.

Back to Top