Support order_by().update() on MySQL/MariaDB.

[Gerben Morsink]

When a UniqueConstraint is used on an IntegerField and we want to update multiple rows of that field at once, we need to make sure we select the right order before the update, since MySQL does not support a deferred update (see: ​https://dev.mysql.com/doc/refman/5.7/en/update.html).

I expected simply doing:

qs.order_by('-integerfield').update(integerfield=F('integerfield')+1) would work, but it seems the Django ORM strips the order_by from the query before execution, it gives a django.db.utils.IntegrityError: (1062, "Duplicate entry for key 'integerfield'").

[Simon Charette]

MySQL seems to be only supported engine to allow UPDATE ... ORDER BY so it doesn't seem to be standard.

• ​https://www.postgresql.org/docs/current/sql-update.html
• ​https://www.sqlite.org/draft/lang_update.html
• ​https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/update_statement.htm

Since it's the documented way of dealing with the lack of deferrable unique constraint on MySQL I guess we could add support for it.

[Simon Charette]

For anyone interested in implementing this feature you'll have to override the as_sql and pre_sql_setup method of django.db.models.sql.compiler.SQLUpdateCompiler in django.db.backends.mysql.compiler.SQLUpdateCompiler to ​avoid clearing the ordering and append the ORDER BY clause to super().as_sql()[0].

[Adam Johnson]

I imagine we'd also want a DB feature flag supports_update_order_by to gate this behaviour.

[David Chorpash]

I would like to give a quick update.

I am able to get the following to work with MySQL on a unique constraint field:

QuerySet.order_by().update()

but I am still working on

QuerySet.update().order_by()

I'm also unsure how to add a feature flag here (I'm still new). I'm not sure if I just missed it in the documentation, but if anyone could point me in the right direction, that would be greatly appreciated!

[Adam Johnson]

I don't think it's possible to support .update().order_by() because update() doesn't return another queryset, but instead actually does the update and returns the number of rows. Supporting only the .order_by().update() syntax seems fine to me.

[David Chorpash]

Replying to Adam (Chainz) Johnson:

I don't think it's possible to support .update().order_by() because update() doesn't return another queryset, but instead actually does the update and returns the number of rows. Supporting only the .order_by().update() syntax seems fine to me.

Thanks for the info!

I added the following ​PR which allows for the use of .update().order_by().

[David Chorpash]

Addressed comments in the PR. Added more test cases to handle a wider variety of use-cases.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 779e615e:

Fixed #31573 -- Made QuerySet.update() respect ordering on MariaDB/MySQL.