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.

Eighty

You can use the negative sign on dates: .order_by('bool_val', '-sort_order') – Hop

Yes, but you can't do that conditionally. – Eighty

Answer 1

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

Eighty

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' ) – Olnek

Answer 2

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

Shiller

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. – Eighty

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')) – Trevor

Answer 3

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

Orourke