How can I include a conditional order_by in django?

Become part of the top 3% of the developers by applying to Toptal https://topt.al/25cXVn

--

Music by Eric Matyas
https://www.soundimage.org
Track title: Over a Mysterious Island

--

Chapters
00:00 Question
01:34 Accepted answer (Score 7)
01:52 Answer 2 (Score 1)
02:42 Answer 3 (Score 0)
03:05 Thank you

--

Full question
https://stackoverflow.com/questions/4427...

Answer 1 links:
[Func]: https://docs.djangoproject.com/en/1.10/r...

--

Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...

--

Tags
#python #django #sorting #django110

#avk47

ACCEPTED ANSWER

Score 10

This is what ended up working:

MyModel.objects.annotate(
    sort_order_true=Case(
        When(bool_val=True, then=('date_a')),
        default=None
    ),
    sort_order_false=Case(
        When(bool_val=False, then=('date_b')),
        default=None
    )
).order_by(
    'sort_order_true',
    '-sort_order_false',
)

ANSWER 2

Score 1

You should be able to use a Func expression to convert your datetimes into timestamps so that you can negate them.

For MySQL this is the UNIX_TIMESTAMP function

MyModel.objects.annotate(
    sort_order=Case(
        When(bool_val=True, then=Func(F('date_a'), function='UNIX_TIMESTAMP')), 
        When(bool_val=False, then=Value(0) - Func(F('date_b'), function='UNIX_TIMESTAMP')), 
    )
).order_by('-bool_val', 'sort_order')

For PostgreSQL this is EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '<datetime>')

MyModel.objects.annotate(
    sort_order=Case(
        When(bool_val=True, then=Func(F('date_a'), function='UNIX_TIMESTAMP')), 
        When(bool_val=False, then=Value(0) - Func('EPOCH FROM TIMESTAMP WITH TIME ZONE', F('date_b'), function='EXTRACT', arg_joiner=' ')), 
    )
).order_by('-bool_val', 'sort_order')

A little cumbersome... and potentially slow. I have not tested this

ANSWER 3

Score 0

This is an another approach for the same problem. I think that this is would work better in null cases(if true or false is not available), but the previous could be helpful in some use cases.

from django.db.models import F
from django.db.models.functions import Coalesce

MyModel.objects.order_by(
    Coalesce(F('date_a'), F('date_b')).desc()
)