Yii2: Upsert support for query builder

[samdark]

Currently there's no way to atomically upsert a record i.e. try to insert a record, if uniqueness constraint failed, update it.

Suggested signature:

public function upsert($table, $columns, &$params);

• MySQL supports it via INSERT ... ON DUPLICATE KEY UPDATE: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
• In PostgreSQL it could be achieved multiple ways: https://dzone.com/articles/performing-upsert-update-or-insert-with-postgresql
• SQLite has INSERT ON CONFLICT REPLACE: http://sqlite.org/lang_conflict.html
• MSSQL, Oracle. Complicated but doable via MERGE + some safeguards: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx and http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/.

More analysis from PostgreSQL: https://wiki.postgresql.org/wiki/UPSERT

[samdark]

Could help solving issues like https://github.com/yiisoft/yii2/issues/11401

[Vovan-VE]

Can and will it support things like MySQL's VALUES(`col`)?

INSERT INTO `table` (..., `col`)
VALUES (..., 1)
ON DUPLICATE KEY UPDATE `col` = `col` + VALUES(`col`);

Does it violate AR concept?
Without this feature stupid MySQL cause stupid problems. One of underlying reasons is that it cannot lock phantom rows. I did try two scenarios to perform INSERT or UPDATE; with non-AI PK and both does hit deadlock due to this reason. I want again to throw MySQL into trash can.

[samdark]

What's VALUES(col)?

[acsfer]

Something like this:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

https://mariadb.com/kb/en/mariadb/values/

[Vovan-VE]

Sorry, I want to ask about referencing to old value:

ON DUPLICATE KEY UPDATE `col` = `col` + ...

Without this ability the subject feature is not so cool. Without this I still need to combine SELECT with INSERT or UPDATE.

Usage of VALUES(`col`) for single row INSERT is not so necessary. Just copy insertable value literally.

[samdark]

Of course, expressions should be supported.

[Locustv2]

From the suggested signature:

public function upsert($table, $columns, &$params);

We will be doing basic queries like:

$queryBuilder->upsert('item', [
    'id' => 1
    'name' => 'some item name',
    'description' => 'lorem ipsum blablabla'
    // other columns
]);

Which will provide something like:

INSERT INTO `item`
(`id`, `name`, `description`, /* other columns */)
VALUES
(...)
ON DUPLICATE KEY UPDATE
`id` = VALUES(`id`),
`name` = VALUES(`name`),
`description` = VALUES(`description`),
/* other columns */

It would also be good to have, like the insert() and batchInsert(), a function batchUpsert(). But then some problem might arise. For example where in some cases there are null values which should not override a previously filled column. How do we apply expression on the UPDATE part:

INSERT INTO `item`
(`id`, `name`, `description`, /* other columns */)
VALUES
(...),
(...),
(...),
(...),
(...)
ON DUPLICATE KEY UPDATE
`id` = VALUES(`id`),
`name` = COALESCE(VALUES(`name`), `name`),
`description` = COALESCE(VALUES(`description`), `description`),
/* other columns */

[sergeymakinen]

@samdark If you’re busy ATM, I would implement the feature next week to resolve #13212.

[samdark]

@sergeymakinen would be great. Thank you.

[sergeymakinen]

A 90% complete (there are things have to be settled along) PR is coming next week. Get ready. ;)

[sergeymakinen]

Here we go: #15276