Hi there, how are you? How is it going? As autumn is here and the weather is cooling down, you are working hard on your Laravel project using mass model insertion or upserts, a very cool feature in Laravel where you can mass insert / update using the sql on duplicate key update feature, and you stumble on a simple requirement like: “Retrieve all the created / updated models right now and do something with them”. You are going to think – Aah, easy – no problem! But then you research and find out – not so easy! Why is that, you ask? Let’s explore this issue in our today’s blog article.
Background Story
I had the same problem. I was working on a system where models were mass created. A parent job e.g A took care of the creation then it dispatched another job e.g B for each one of these models. Job B made an api request and if successfully, it populated some attributes of the model.
Now, we all know, saving a few models using the save() or create() methods of Laravel is no big deal. But imagine you have to deal with thousands of models, then save() or create() is not an option. The answer is to mass insert all models. But how to retrieve them? There is no such functionality in Laravel to mass insert and retrieve models. Yes you could retrieve them if they were e.g created using an user_id but this was not my case. There was no way I could refer to these models directly because they were later associated with the users.
Searching For Solutions
Let’s explore some ideas on how to deal with such a problem.
- Getting the last inserted id
- Creating models using preconfigured ids
- Using an unique identifier
- Using some custom functionality to keep track of the inserted models
1. Getting the last inserted id
The first option is possible, you retrieve the mass inserted models using app(‘db’)->getPdo()->lastInsertId() or insertGetId() functionality, but nonetheless this is dangerous! If any other query is inserting models then the results might not be accurate.
2. Creating models using preconfigured ids
This is another approach I have seen that people use. Might work out with an ordered uuid column as a primary key. For numeric ids this is going to be messy. If you have parallel processes running or database transactions this is going to be a nightmare to handle. Although the solution might be feasible it is adding an extra overhead of handling conflicts.
3. Using an unique identifier
Now we could use something like a unique string right? We could use that value, store it on an extra column and use that for retrieval but do you hate that very small probability of things going wrong? Me too.
4. Using some custom functionality to keep track of the inserted models
I am going to steal the idea of the 3rd approach and alter it a little bit. It is a good idea but instead of using unique random strings I am going to use a model id like this:
- Create a new model called ModelBatchInsert and a migration.
- Add an extra column just as in the 3rd example on the desired table
- Mass create models with the created ModelBatchInsert->id
- Retrieve all models using that id
Using 1 model as a batch to represent 10.000 models inserted in the database is the solution! We don’t need to worry about parallel processes, database transactions or anything else. You can simply create one instance of a ModelBatchInsert and use that to solve our problem. It’s time to write some code!
Implementation
The first step is as described above, to create a model and a migration. We can do that by running this command: php artisan make:model ModelBatchInsert -m
Next, we need to specify the table structure in the migration:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('model_batch_inserts', function (Blueprint $table) {
$table->id();
$table->string('model_type')->index();
$table->timestamps();
$table->softDeletes();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('model_batch_inserts');
}
};
We also need another model and migration for demonstration purposes. There we are going to add a model_batch_insert_id column. We are going to use this column for mass insertion.
php artisan make:model ComposedProduct -m
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('composed_products', function (Blueprint $table) {
$table->id();
$table->unsignedInteger('model_batch_insert_id')->index();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('composed_products');
}
};
Let’s add some helper methods inside the ModelBatchInsert class so that we can simplify creation / retrieval of mass inserted models.
<?php
namespace App\Models;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Collection as EloquentCollection;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Collection as SupportCollection;
use Illuminate\Support\Facades\Schema;
/**
*
* @property int $id
* @property class-string $model_type
*/
class ModelBatchInsert extends Model
{
use HasFactory, SoftDeletes;
protected $guarded = ['id'];
/**
* @param SupportCollection $models
* @return EloquentCollection
*/
public function createMany(SupportCollection $models): EloquentCollection
{
if ($models->isNotEmpty() && is_subclass_of($this->model_type, Model::class)) {
$model = $this->model_type::query()->getModel();
if (Schema::hasColumn($model->getTable(), $this->getForeignKey())) {
$models = $this->addTimestamps($models);
$this->model_type::query()->insert($models->toArray());
}
}
return $this->models();
}
/**
* @param SupportCollection $models
* @return SupportCollection
*/
private function addTimestamps(SupportCollection $models): SupportCollection
{
$now = $this->fromDateTime(Carbon::now());
$timestampColumns = $this->getTimestampColumns();
return $this->addAndConvertTimestamps($models, $timestampColumns, $now);
}
/**
* @return SupportCollection
*/
private function getTimestampColumns(): SupportCollection
{
$timestampColumns = collect();
if (is_subclass_of($this->model_type, Model::class)) {
$model = $this->model_type::query()->getModel();
$usesTimestamps = $model->usesTimestamps();
if ($usesTimestamps) {
$timestampColumns->push(
$model->getCreatedAtColumn(),
$model->getUpdatedAtColumn(),
);
}
}
return $timestampColumns;
}
/**
* @param SupportCollection $models
* @param SupportCollection $timestampColumns
* @param string $now
* @return SupportCollection
*/
private function addAndConvertTimestamps(
SupportCollection $models,
SupportCollection $timestampColumns,
string $now
): SupportCollection
{
return $models->map(function (array $modelAttributes) use ($timestampColumns, $now) {
// setting model_batch_insert_id
if (!isset($modelAttributes[$this->getForeignKey()])) {
$modelAttributes[$this->getForeignKey()] = $this->id;
}
// Set model created_at, updated_at timestamps if not present
foreach ($timestampColumns as $timestampColumn) {
if (!isset($modelAttributes[$timestampColumn])) {
$modelAttributes[$timestampColumn] = $now;
}
}
// Convert any Carbon date to database timestamp format
foreach ($modelAttributes as &$value) {
if ($value instanceof Carbon) {
$value = $this->fromDateTime($value);
}
}
return $modelAttributes;
});
}
/**
* @return EloquentCollection
*/
public function models(): EloquentCollection
{
if (is_subclass_of($this->model_type, Model::class)) {
$query = $this->model_type::query();
$column = $this->getForeignKey();
if (Schema::hasColumn($query->getModel()->getTable(), $column)) {
return $query->where($column, $this->id)->get();
}
}
return EloquentCollection::make();
}
}
Testing
All we need now is the logic out. For that I am going to create a phpunit test called ExampleTest.php and add a test_model_batch_creation method.
<?php
namespace Tests\Feature;
use App\Models\ComposedProduct;
use App\Models\ModelBatchInsert;
use Illuminate\Support\Collection;
use Tests\TestCase;
class ExampleTest extends TestCase
{
public function test_model_batch_creation()
{
/**
* @var ModelBatchInsert $modelBatchInsert
*/
$modelBatchInsert = ModelBatchInsert::query()->create(['model_type' => ComposedProduct::class]);
$number = 10000;
$massInsertionModels = Collection::times($number, fn() => []);
$composedProducts = $modelBatchInsert->createMany($massInsertionModels);
$this->assertTrue($composedProducts->count() === $number);
}
}
Let’s run the test and see what happens.
The test ran successfully and the database contains 1 ModelBatchInsert
And 10.000 ComposedProducts
Awesome, let’s add another test for the models() method.
<?php
namespace Tests\Feature;
use App\Models\ComposedProduct;
use App\Models\ModelBatchInsert;
use Illuminate\Support\Collection;
use Tests\TestCase;
class ExampleTest extends TestCase
{
public function test_model_batch_creation()
{
/**
* @var ModelBatchInsert $modelBatchInsert
*/
$modelBatchInsert = ModelBatchInsert::query()->create(['model_type' => ComposedProduct::class]);
$number = 10000;
$massInsertionModels = Collection::times($number, fn() => []);
$composedProducts = $modelBatchInsert->createMany($massInsertionModels);
$this->assertTrue($composedProducts->count() === $number);
}
public function test_model_batch_insert_models_method()
{
/**
* @var ModelBatchInsert $modelBatchInsert
*/
$modelBatchInsert = ModelBatchInsert::query()->find(1);
$this->assertTrue($modelBatchInsert->models()->count() === 10000);
}
}
Running the test and success!
Conclusion
By storing a representative entity in the database like the ModelBatchInsert per 1 batch of mass inserted models we can now easily mass create and retrieve models on the fly without worrying about wrong results, conflicts or low chance probabilities which can actually occur. Same principle works for upserts but it depends on your use case if you truly need it. That’s it folks, feel free to play around with it and enjoy a nice and healthy breakfast. Happy coding!
Useful Links:
MySQL Docs:
MySQL 8.0 Reference Manual :: 13.2.7 INSERT Statement
MySQL 8.0 Reference Manual :: 13.2.7.2 INSERT … ON DUPLICATE KEY UPDATE Statement
Stackoverflow discussions:
How to get the data of the inserted multiple rows in Laravel Eloquent ORM – Stack Overflow
Bulk insert and get returned ids laravel – Stack Overflow
Laracasts:
We have helped 20+ companies in industries like Finance, Transportation, Health, Tourism, Events, Education, Sports.
