add into insert and batchisert new parameter

[dizews]

Hello. Sometimes I need to insert or update rows. I can do it using 'ON DUPLICATE KEY UPDATE'.

what do you think?

Funding

• You can sponsor this specific effort via a Polar.sh pledge below
• We receive the pledge once the issue is completed & verified

[samdark]

It's not the same for all databases we support. Need a research at least for:

• MySQL.
• Cubrid.
• MSSQL.
• Oracle.
• PostgreSQL.
• SQLite.

Also it would be great to have an idea on how to support something like that for noSQL.

[arisk]

In MySQL this causes problems when using statement-based replication. https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

[Ragazzo]

yes it is not supported by common RDBMS, but it already was discussed and we added exception for this case like it is done in django afaik, see this issue, and this IntegrityException

[arisk]

@Ragazzo this is not a case of integrity constraint violation. The problem is that if you're using MySQL this statement will cause a warning in the logs but it will still do the insert/update. There are some cases where this might lead to unexpected behavior. Please see http://bugs.mysql.com/bug.php?id=58637, http://docs.oracle.com/cd/E17952_01/refman-5.5-en/replication-rbr-safe-unsafe.html

[Ragazzo]

@arisk my answer was for @dizews and @samdark about ON DUPLICATE KEY UPDATE )

[klimov-paul]

Actually the requested functionality in MySQL is solved via 'REPLACE' statement, which implementation in the core has been rejected long ago as non SQL standard. I don't think we should support this.

[samdark]

@klimov-paul these aren't equal in MySQL but yes, generally I don't think we'll support non-standard SQL for each database.

[klimov-paul]

these aren't equal in MySQL

Ok, not equal - similar.

Still at the moment this feature can be achieved by extending QueryBuilder and override batchInsert() method:

class MyQueryBuilder extends yii\db\mysql\QueryBuilder
{
    public function batchInsert($table, $columns, $rows)
    {
        $sql = parent::batchInsert($table, $columns, $rows);
        $sql .= 'ON DUPLICATE KEY UPDATE';
        return $sql;
    }
}

[qiangxue]

We can add an additional parameter to batchInsert() whose content will be appended to the generated SQL. But I'm not sure if this is sufficient for all DBMS.

[klimov-paul]

What if someone wants ot append extra statement before generated SQL or before table name of after 'update' keyword? I don't think this is correct path.

[qiangxue]

What if someone wants ot append extra statement before generated SQL or before table name of after 'update' keyword?

Well, that's what I'm not sure about. Should check every DBMS to make judgement.

[disem]

Any updates on this? As for me there is no reason to avoid supporting database specific features or there should be only one adapter called SQL with SQL only features.

[cyphix333]

I would love to know if the discussion of this has advanced any further as well.

[klimov-paul]

You can always use following code:

$db = Yii::$app->db;
$sql = $db->queryBuilder->batchInsert($table, $fields, $rows);
$db->createCommand($sql . ' ON DUPLICATE KEY UPDATE')->execute();

[ToRvaLDz]

Why not use:

$db = Yii::$app->db;
$sql = $db->queryBuilder->batchInsert($table, $fields, $rows);
$db->createCommand(str_replace("INSERT INTO ","REPLACE INTO",$sql))->execute();

[lubosdz]

The REPLACE statement speeds up SQL queries 5-10x, would be nice if it could be directly supported. Currently only via workaround as suggested by @ToRvaLDz.

[samdark]

Related to https://github.com/yiisoft/yii2/issues/13879

[swartzlib7]

Firstly this is a cool thread so thank you.

I'm disappointed that PostgreSQL doesn't support a "REPLACE" type operation such as that of MySQL. I'm considering dropping PostgreSQL from my project and reverting back to MySQL because of this, but that's just being a bit emotional about it. Do you guys need help getting this into Yii2? Are there plans and by when?

I am managing IP Address Data and am expanding subnets into record sets of tens of thousands using batchInsert(). If a subnet changes size I need to rerun the batchInsert() operation to bring the data into sync. I use a well designed Relational Database Schema with Foreign Keys etc.

This becomes a stop unless I write some custom SQL which I'm trying to avoid at all cost due to the fact that I'm using the Yii2 Framework.

[samdark]

@swartzlib7 it's planned for 2.1.

[swartzlib7]

@samdark thanks for the feedback! I used the pattern given above (klimov-paul commented on Apr 28, 2015) and it works well. I look forward to 2.1 even though it's still in the oven... ;-)

Have a good one!

[samdark]

Done for insert (not batch).

[dizews]

good news, thanks!

[Tigrov]

There is butchInsert() and need batchUpsert(), see #92