Mass inserOrUpdate Elequent and DB

[webbby]

I don't know how to put it guys and I'm sure I am not the first but no convenient multiple records insertion and multiple insertOrUpdate in 2018 in a framework that is extremely popular is just unacceptable. Pardon me if I'm missing something but I just don't get it ... You add some relatively minor features to the framework with each release when the ORM and the DB Builder doesn't support widely used and important features at least not in a convenient way.

I am facing really unpleasant issue where I need to insert or update already related structures of data and there's nothing in the documentation of how to insert or update multiple records in single request and moreover if we add constraints and many to ... relationships the situation gets even worse. So in my best scenario I have to make 3-4 requests per table and its constraints if I'm lucky to find enough information of your DB tool and efficient enough SQL queries or if I follow your documentation thousands of requests

Is it so hard to have:

$isSuccessful = Model::inserOrUpdate($arrayOfRecords);
$allAffectedRecordIds = DB::inserOrUpdateGetIds(
    $tableName, $arrayOfRecords, $excludedUpdateColumns
) ;
// and the corresponding cases for many to ... relationships

What am I talking about perhaps I am asking too much. At least can we get Model::insert with auto created_at

[staudenmeir]

You are always welcome to submit a pull request. Using a tone like that will certainly not encourage anyone to implement this feature.

[webbby]

You are always welcome to submit a pull request. Using a tone like that will certainly not encourage anyone to implement this feature.

The fact that after so many years this basic feature is missing is frustrating enough. Like somebody would have implemented it even without the unpleasant tone.

[staudenmeir]

Apparently, other users disagree with you on the importance of this feature.

[webbby]

Apparently, other users disagree with you on the importance of this feature.

Which is very disturbing don't you think? It shows that they just write SQL queries and passively wait on Laravel team to finally do something about it. But the more disturbing fact is they don't and are waiting on enough users to initiate request.

[staudenmeir]

I don't see why that would be disturbing. Maybe this is just a rare use case and only very few people have ever needed such a feature.

[sisve]

What you call "insert or update" is often called "upsert" (update or insert) and is a supported in many database engines, albeit with proprietary syntax.

The MERGE statement is part of a newer sql standard, but it presumes you already have the data you want inserted in another table, so we would at least need temporary tables to do that, and do all our supported database platforms support temporary tables?

If we were to attempt batch inserts we would could bunch some records together into a single INSERT INTO (col1, col2) VALUES (@record1_col1, $record1_col2), (@record2_col1, @record2_col2), but we would run into limitations on maximum number of sql parameters supported. This also requres that all the records we need to upsert have the same structure, we can't have one record with an extra column. But it can be done with these limitations.

However, once we move into upserts we run into problems on how to write the ON DUPLICATE KEY UPDATE (using mysql as example) to handle multiple records. Any ideas how this would done? Or do we need to fall back into one INSERT INTO for every record?

And then we have yet another problem; insertOrUpdateGetIds is expected to return the identifiers of the records upserted. This basically forces us to execute one INSERT INTO per record to be able to interleave calls to SELECT LAST_INSERT_ID().

How have you solved all these problems in your solution where you only use 3-4 requests per table?

[staudenmeir]

For MySQL, there is this package: https://github.com/yadakhov/insert-on-duplicate-key

[webbby]

For MySQL, there is this package: https://github.com/yadakhov/insert-on-duplicate-key

Thank you for your concern.

The creator if the library says

created_at and updated_at will not be updated automatically. To update you can pass the fields in the insert array.

Perhaps I am mistaking but I don't know how is updated_at going to be updated when you pass it with the inserted properties but I will give it a try. If one pass it to the updated properties the risk is a record is going to have a new updated_at without being actually modified. Anyway thank you.

[webbby]

@sisve

How have you solved all these problems in your solution where you only use 3-4 requests per table?

In my case The ON DUPLICATE KEY upsert query starts with conditional updated columns

ON DUPLICATE KEY
updated_at=IF(
   name=VALUES(name) && color=VALUES(color) && ...., 
   updated_at, VALUES(updated_at) 
)

and moves with the updated ones and we have 1 request Then because I know I won't have time collision with previous records I take all the records with the updated_at value.

$affectedRecords = DB::table('table')
                    -> select('id')
                    -> where('updated_at', '=', $updatedAtValue)
                    -> get();

2 requests

This isn't a knew problem in general there are ORMs that have solved the problem from a long time. I'm not an expert on ORMs there are many cases and specifics that are taken into consideration but mass upsert is an everyday problem. When Laravel doesn't support such feature the developers will have to write queries or switch to java environment or something else that has taken care of the problem.

[sisve]

@webbby So you basically know things about your code, you have limitations in your solution that you've accepted. You know that there will be no conflicts, no one else is touching that updated_at column while you're executing your code.

A framework cannot make such assumptions. A framework solution needs to work without assuming you're the only writer to the table. A framework cannot assume that you will always have an update_at column either.

Have you tried writing sql statements that would work without the update_at column? Because that's what the framework solution have to do.

Can you provide us links to other orms that supports these features; batch-upserting and returning the modified identifiers? We can probably look at how they have solved it and build an appropriate Laravel solution.

[webbby]

@sisve I am aware that my solution is not appropriate. There's always a way this to be achieved just let the user decide how to pay the price but at least to have options. For instance just hypothetically if I have found in laravel documentation the following section Mass Insert or Update Laravel can provide batch upsert for users who deal with large data. In order for that to be achieved user must provide helper column in upsert tables which would be ignored in the end result. In your migrations add

$table->upsertUid();

If I had the option I would gladly use it.

[sisve]

You mentioned that other orms has solved this problem. Can you tell us about any specific orm? It would be easier to implement this if we knew how others have implemented it.

[staudenmeir]

I've created an UPSERT package for all databases: https://github.com/staudenmeir/laravel-upsert