How can I include a conditional order_by in django?

Question

I need to sort two parts of a queryset differently based on a boolean field. - Rows that have bool_val == True should come first, and be sorted by date_a, ascending. - Rows that have bool_val == False should come second, and be sorted by date_b, descending.

The closest I've gotten is

MyModel.objects.all().annotate(
    sort_order=Case(
        When(bool_val=True, then=('date_a')), 
        default='date_b')
    ).order_by('-bool_val', 'sort_order')

But that sorts them all in the same order. If the value I needed to sort by were numerical, then I would multiply one set by -1 in my annotation, but they're date values, so that won't work.

I've also looked into creating two separate querysets and combining them, but union() isn't available until 1.11 and I have to support 1.10, and other methods of combining querysets that I've found either don't preserve order or don't result in a queryset (or both). (I'm not clear on whether union() preserves order or not, but it's moot, so I didn't dig into it much, either.) This has to be a django QuerySet object at the end.

You can use the negative sign on dates: `.order_by('bool_val', '-sort_order')` — Moses Koledoye, May 30 '17 at 22:05

score 8 · Accepted Answer · answered May 31 '17 at 16:13

8

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',
)

answered May 31 '17 at 16:13

Charlotte Mays

325
4
11

Can we use a string field on When? like I want to use sort with a column named str_val which have a value in one record as `ASDF` that i need at first then by id `MyModel.objects.annotate( sort_order_true=Case( When(str_val="ASDF", then=(1)), default=0 ) ).order_by( 'sort_order_true' )` – Vijay Aug 01 '22 at 10:34

Iain Shelvington · Answer 2 · 2017-05-31T00:32:53.900

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

edited May 31 '17 at 00:32

answered May 31 '17 at 00:26

Iain Shelvington

31,030
3
31
50

This was a good thought but I ran into trouble with django trying to interpret `'EPOCH FROM TIMESTAMP WITH TIME ZONE'` as a field name. I tried a couple variants but ended up finding a more elegant solution; see the answer I posted. – Charlotte Mays May 31 '17 at 16:14
Thanks for proposing this solution. I had the same problem as @CharlotteMays and I ended up using this: `Extract('date_a', 'epoch')` in place of `Func(F('date_a'), function='UNIX_TIMESTAMP'))` – Ramon Blanquer Jul 29 '20 at 18:38

score 0 · Answer 3 · answered Sep 20 '22 at 06:37

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()
)

How can I include a conditional order_by in django?

3 Answers3