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.

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

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