I have an array like this one :
0 => array:2 [
"name" => "Data1"
"type" => "value1"
],
1 => array:2 [
"name" => "Data2"
"type" => "value2"
]
i want to insert them in single query in the database and retrieve their ids without any additional query.
So far i have tried insertGetId
MyModel::insertGetId($array)
but i have noticed it inserts bulk rows but return the last id.
Well You Can Get The Last Id from the table .. Then After The Insertion Add The Last id To The Count of your array .. But you Will face a problem and that is if you have 2 or more users inserted some records into this table at the same time .. so you can use The Transaction
try{
DB::beginTransaction();
// 1- get the last id of your table ($lastIdBeforeInsertion)
// 2- insert your data
Model::insert($array);
// 3- Getting the last inserted ids
$insertedIds = [];
for($i=1; $i<=theCountOfTheArray; $i++)
array_push($insertedIds, $lastIdBeforeInsertion+$i);
});
DB::commit();
}catch(\Exception $e){
DB::rollback();
}
or
DB::transaction(function() {
// 1- get the last id of your table ($lastIdBeforeInsertion)
// 2- insert your data
Model::insert($array);
// 3- Getting the last inserted ids
$insertedIds = [];
for($i=1; $i<=theCountOfTheArray; $i++)
array_push($insertedIds, $lastIdBeforeInsertion+$i);
});
Database Transaction Documentation
Very Useful Article About Database Transactions
You Can make a unique Column and Call it for Example unique_bulk_id
.. This will hold randomly generated string for the inserted data .. after the insertion you can get the inserted data by This unique_bulk_id
.
I use this method to insert non-duplicate data.
But this method consumes performance.
class TestController extends Controller
{
public function test()
{
DB::transaction(function () {
$insertedIds = [];
//the name key is unique, so i use the firstOrCreate method
for ($i = 0; $i < 10; $i++) {
$tag = Tags::firstOrCreate(['name' => 30 + $i]);
$id = $tag->id;
array_push($insertedIds, $id);
}
dump($insertedIds);
});
}
}