Laravel - multi-insert rows and retrieve ids

Question

I'm using Laravel 4, and I need to insert some rows into a MySQL table, and I need to get their inserted IDs back.

For a single row, I can use ->insertGetId(), however it has no support for multiple rows. If I could at least retrieve the ID of the first row, as plain MySQL does, would be enough to figure out the other ones.

Solution 1

As user Xrymz suggested, DB::raw('LAST_INSERT_ID();') returns the first.

According to Schema api insertGetId() accepts array

public int insertGetId(array $values, string $sequence = null)

So you have to be able to do

DB::table('table')->insertGetId($arrayValues);

Thats speaking, if using MySQL, you could retrive the first id by this and calculate the rest. There is also a DB::getPdo()->lastInsertId(); function, that could help.

Or if it returened the last id with some of this methods, you can calculate it back to the first inserted too.

EDIT

According to comments, my suggestions may be wrong.

Regarding the question of 'what if row is inserted by another user inbetween', it depends on the store engine. If engine with table level locking (MyISAM, MEMORY, and MERGE) is used, then the question is irrevelant, since thete cannot be two simultaneous writes to the table.

If row-level locking engine is used (InnoDB), then, another possibility might be to just insert the data, and then retrieve all the rows by some known field with whereIn() method, or figure out the table level locking.

Solution 2

It's mysql behavior of last-insert-id

Important
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

u can try use many insert and take it ids or after save, try use $data->id should be the last id inserted.

Solution 3

If you are using INNODB, which supports transaction, then you can easily solve this problem. There are multiple ways that you can solve this problem.

Let's say that there's a table called Users which have 2 columns id, name and table references to User model.

Solution 1

Your data looks like

$data = [['name' => 'John'], ['name' => 'Sam'], ['name' => 'Robert']]; // this will insert 3 rows

Let's say that the last id on the table was 600. You can insert multiple rows into the table like this

DB::begintransaction();
User::insert($data); // remember: $data is array of associative array. Not just a single assoc array.
$startID = DB::select('select last_insert_id() as id'); // returns an array that has only one item in it
$startID = $startID[0]->id; // This will return 601
$lastID = $startID + count($data) - 1; // this will return 603
DB::commit();

Now, you know the rows are between the range of 601 and 603 Make sure to import the DB facade at the top using this

use Illuminate\Support\Facades\DB;

Solution 2
This solution requires that you've a varchar or some sort of text field

$randomstring = Str::random(8);
$data = [['name' => "John$randomstring"], ['name' => "Sam$randomstring"]];

You get the idea here. You add that random string to a varchar or text field.

Now insert the rows like this

DB::beginTransaction();
User::insert($data);
// this will return the last inserted ids
$lastInsertedIds = User::where('name', 'like', '%' . $randomstring)
                         ->select('id')
                         ->get()
                         ->pluck('id')
                         ->toArray();
// now you can update that row to the original value that you actually wanted
User::whereIn('id', $lastInsertedIds)
      ->update(['name' => DB::raw("replace(name, '$randomstring', '')")]);
DB::commit();

Now you know what are the rows that were inserted.

Comments

[Gustavo Silva]

Yes, but I want to use multiple insertion to improve performance.

[Xrymz]

by LAST_INSERT_ID() you get first ID of the bunch that was inserted into the database. Then just increment that by 1 for every row you have inserted.

[Gustavo Silva]

Actually the array accepted represents a single row. If you try to insert a nested array with more than one row it will throw an error.

[Jarek Tkaczyk]

Yes, only it's screwed up and requires fix. Otherwise it throws error for batch insert, since it can't process the bindings etc

[Gustavo Silva]

What if someone inserts into the table before I get the last ID? Can it cause problems?

[Gustavo Silva]

Nevermind, I have found it out. Actually the last_inserted_id used by MySQL is relative to each connection, so even if other clients insert new rows, our last inserted id will still be the same.

[Jarek Tkaczyk]

In the meantime I made a fix to the repo: github.com/laravel/framework/pull/5290 so with this you can rely on insertGetId batch statement and it will return first id of that batch insert.

[I am L]

@Xrymz the downside of that is, what if there's a multiple users inserting a multiple data at the same time? you can't just increment them coz it might return the inserted data of other users

[tylik]

has anyone actually found a proper answer to this? Is there really no way to do that with raw sql query using Laravel query builder?

[Musa Haidari]

As of now insertGetId() accepts only one record, not multiple, laravel 7

[Bo Terham]

so the transaction prevents race conditions on the table? there is no possibility that another use comes inbetween because of the transaction? how does that work? does the entire table get locked?