Consistent ordering of database Booleans

[Shai Berger]

Hi,

A ticket[1] that was wontfix'd almost three years ago says,

"""
When using a model with a boolean field, you can retreive a QuerySet and order
it on the boolean field. In Postgres, the true comes first, in SQLite, false
comes first.
"""

The reason to wontfix was that this difference between database behaviors would
require a lot of work for a relatively unimportant use-case -- a developer
switching between databases.

Since then, three things happened:

1) A further comment on the ticket noting a more important use-case --
pluggable apps;

2) Since Django 1.7, it is easy to add transforms to the ORM, even ones which
behave differently on different backends;

3) Lately, in an additional comment on the ticket, user felipeochoa suggested
a workaround (assume a model `Transaction` with a BooleanField `submitted`):

Transaction.objects.annotate(
submitted_as_0_1=Case(When(submitted=True, then=Value(1)),
default=Value(0),
output_field=PositiveSmallIntegerField())
).order_by('submitted_as_0_1')

I think we can combine the last two, and "solve" the issue with a transform
defined on boolean fields, so that users will be able to write

Transaction.objects.order_by('submitted__consisitent_ordered')

Thoughts?

Shai.

[1] https://code.djangoproject.com/ticket/19726

[Josh Smeaton]

I thought I'd responded to that ticket with a similar suggestion but I must have clicked away before submitting.

Yes, providing a transform would be ideal. Unfortunately order_by does not support __lookup syntax. Adding __lookup syntax support to F() is tracked here https://code.djangoproject.com/ticket/24747.

It shouldn't be a difficult ticket to solve, and that will provide everything needed to solve boolean consistent ordering.

Cheers