Mysql ON DUPLICATE KEY UPDATE

[Flowman]

Can we extend the mysqli driver with https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html ?

Example we have insertObject and updateObject function. But it would be nice to have a insertUpdateObject(table, data, update-data)

Or is there a way to extend the query driver from a component?

[richard67]

Hello @Flowman ,

thank you for your feature request.

The good news is that ON DUPLICATE KEY UPDATE is supported by MySQL at least since version 5.6, and it is also supported by MariaDB:

• https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
• https://mariadb.com/kb/en/insert-on-duplicate-key-update/

The right way to do it will indeed be to extend the database drivers. For Joomla 4 it would need to be done for the MySQLi and the MySQL (PDO) drivers.

For Joomla 3 it would need to do it also for the native mysql driver, but as we have to support lower MySQL versions than 5.6 with Joomla 3, I think we can do it only for Joomla 4.

The database drivers are maintained in another GitHub repository: https://github.com/joomla-framework/database .

I suggest that you open an issue there with a title or description to make clear that it's a feature request and that it is for the 2.0-dev branch of that repository, which is the branch used for Joomla 4.

Thanks in advance if you do that.

Let me know if you can't do that for some reason, then I can do it.

Of course, as you always can execute an insert statement which is a string, you could build such a statement with an ON DUPLICATE KEY clause in a component and then just execute the statement.

But that would not really be clean and nice, it would be better to have an API for that using the right parameters and allowing dynamic bind (aka prepared statements).

[richard67]

@Flowman The CMS core has to support PostgreSQL, too.

It seems there is something similar for PotsgreSQL which can do the same job as the ON DUPLICATE KEY UPDATE for MySQL/MariaDB:

• https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql
• https://www.postgresqltutorial.com/postgresql-upsert/

So it can and should be implemented in a way so that it can used with PostgreSQL, too.

[richard67]

P.S. As soon as it will be merged in the 2.0-dev branch of the framework repository, and a new beta or whatever release has been made there, a pull request can be made for the CMS to update the database package (composer dependency). I can keep an eye on it and might even be the one who implements it in the framework.

[Flowman]

I started writing it a little bit and my idea was to use insertUpdate(table, object, array(columns to update if exists)). and create an query->onduplicate(data) or something similar.

[richard67]

As said, I suggest to use an issue on https://github.com/joomla-framework/database for it, also to discuss implementation. Of course you can also make a PR there.

[richard67]

joomla-framework/database#226 .. Thanks @Flowman . Could you add your ideas you have mentioned above for the implementation there, too? Thanks in advance.