Download the PHP package lapaliv/laravel-bulk-upsert without Composer

Laravel Bulk Upsert with eloquent's events

Annotation

How often do you need to insert a collection of models? I have this task quite often. Laravel has a solution for mass insert/update/upsert, but it uses a different algorithm than eloquent. For example, when we are using $model->create() we can supplement the data in the observers, and it doesn't work when we use Model::query()->insert().

The second problem is in the number of fields. We need to align the fields before passing them to insert/update/upsert method of the builder. This is not always convenient.

The third problem is in the way get the inserted rows back after inserting. Laravel doesn't return them. Of course, it won't be a big deal if you have only one unique column, but you will need some time to write quite large SQL query to select them in another case.

Because of the above I have written this library which solves these problems. Using this library you can save a collection of your models and use eloquent events such as creating/created, updating/updated, saving/saved, deleting/deleted, restoring/restored, forceDeleting/forceDeleted at the same time. And you don't need to prepare the number of fields before.

In simple terms, this library runs something like this:

but with a few queries to the database per chunk.

Features

• Creating / Updating / Upserting a collection with firing eloquent events:

  • creating / created,
  • updating / updated,
  • saving / saved,
  • deleting / deleted,
  • restoring / restoried;
  • forceDeleting / forceDeleted;

  and some new events:

  • creatingMany / createdMany,
  • updatingMany / updatedMany,
  • savingMany / savedMany,
  • deletingMany / deletedMany,
  • restoringMany / restoredMany;
  • forceDeletingMany / forceDeletedMany;
• Automatically align transmitted fields before save them to the database event if you don't use eloquent events.
• Select inserted rows from the database

Documentation for version 1.x

The documentation for version 1.x you can see here

Requires

• Database:
  • MySQL: 5.7+
  • PostgreSQL 9.6+
  • SQLite 3.32+
• PHP: 8.0+
• Laravel: 8.0+

Installation

You can install the package via composer:

Get started

Use the trait Lapaliv\BulkUpsert\Bulkable in your model(s):

Usage

Make the instance

There are four ways how you can make an instance

Creating / Inserting

Preparing the data

You can just create these users.

You can create and get back these users.

You can accumulate rows until there are enough of them to be written.

Updating

Preparing the data

You can just update these users.

You can update these users and get back a collection of the models.

You can accumulate rows until there are enough of them to be written.

Extra way

There is an extra way how you can update your data in the database:

This way loads the data from database and updates found rows by the query.

Upserting (Updating & Inserting)

Preparing the data

You can just upsert these users.

You can upsert these users and get back a collection of the models.

You also can accumulate rows until there are enough of them to be written.

Force/Soft Deleting (since v2.1.0)

Preparing the data

You can just delete these users. If your model uses the trait Illuminate\Database\Eloquent\SoftDeletes, then your model will delete softly else force.

Or you can force delete them.

You also can accumulate rows until there are enough of them to be deleted.

Listeners

The order of events

The order of calling callbacks is:

• onSaving
• onCreating or onUpdating
• onDeleting
• onForceDeleting
• onRestoring
• onSavingMany
• onCreatingMany or onUpdatingMany
• onDeletingMany
• onForceDeletingMany
• onRestoringMany
• onCreated or onUpdated
• onDeleted
• onForceDeleted
• onRestored
• onCreatedMany or onUpdatedMany
• onDeletedMany
• onForceDeletedMany
• onRestoredMany
• onSavedMany

How listen to events

There are three ways how you can listen events from the library:

How listen to events: Bulk Callbacks

How listen to events: Model Callbacks

You also can use model callbacks. They are almost the same. Just remove the prefix on. For example:

How listen to events: Observer

You also can use observers. For example:

Example

You can observe the process. The library supports the base eloquent's events and some extra which are give you the access to the collection of models. Listeners with collections accept extra parameter with type BulkRows. This is a collection of class Lapaliv\BulkUpsert\Entities\BulkRow which contains your original data (the property original), the model (the property model) and the unique attributes which were used for saving (the property unique). It can help you to continue your saving if, for example, you had some relations in your data.

Let's look at the example:

In this example you have a chain. After upserting the user, the library will run UserObserver::savedMany() where the code will prepare comments and will upsert them.

API

TODO

• Bulk restoring
• Bulk touching
• Bulk updating without updating timestamps
• Supporting DB::raw() as a value
• Support a custom database driver
• Update rows and return the number of updated ones

Tests

You can check the actions or run it on your laptop: