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.

Answer 1

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.

Answer 2

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.

Answer 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.