Order of WHERE clauses do not match the order of the arguments to .filter().

[Tommy Li]

With the following sample model

class MyModel(models.Model):
    a_field = models.IntegerField()
    b_field = models.IntegerField()

If I run the following query:

MyModel.objects.filter(b_field = 1, a_field = 2)

the SQL that it generates is

SELECT "mymodel"."a_field", "mymodel"."b_field
FROM "mymodel"
WHERE ("mymodel"."a_field" = 2 AND "mymodel"."b_field" = 1)

I would expect the order of the clauses in the WHERE statement to match the order of arguments passed to the filter statement. In my case, b_field has a much higher cardinality than a_field and I want the query to use an index I have on (b_field, a_field)

After some experimentation, it looks like the WHERE clauses are ordered alphabetically by column name. Is that explicitly intended, or is it just a side effect of some implementation detail?

A workaround is to run

MyModel.objects.filter(b_field = 1).filter(a_field = 2)

But this is pretty inconvenient if you just want to run a .get, you would have to build out a verbose .filter().filter().first() query

[Mariusz Felisiak]

Thanks for this ticket.

After some experimentation, it looks like the WHERE clauses are ordered alphabetically by column name. Is that explicitly intended, or is it just a side effect of some implementation detail?

QuerySet.filter() uses Q() objects. We added sorting to kwargs to make Q.deconstruct() deterministic on Python 3.5, see #29125. Django 3.0+ supports only Python 3.6+, so we could theoretically revert this fix, but this will generate migrations for existing project. I think it isn't worth.

I would expect the order of the clauses in the WHERE statement to match the order of arguments passed to the filter statement. In my case, b_field has a much higher cardinality than a_field and I want the query to use an index I have on (b_field, a_field)

Clauses ordering should not affect indexes, as far as I'm aware databases will use a (b, a)-index in both cases b=1 AND a=2 and a=2 AND b=1.