Django queryset order_by absolute value

Question

I have a queryset that has a description attribute and a balance attribute. Currently, when I produce the queryset it is ordered from smallest to largest - -

Model.objects.order_by('balance')

#output

Thing1          -120000
Thing2             -300
Thing3             7000
Thing4           100000

What I would like to order by is decending absolute value such that the result is:

#Desired Output

Thing1          -120000
Thing4           100000
Thing3             7000
Thing2             -300

I've tried passing various methods inside the order_by() but I think it needs to be an SQL type argument - which I can't figure out. I found this which uses a func expression when annotating. I couldn't get it to work with the order_by and the docs didn't help much.

Is there a way to order a queryset in such a way?

neverwalkaloner · Accepted Answer · 2018-06-16T05:42:39.760

13

You can annotate absolute values and order by annotated value. You can use - sign to indicate descending order:

from django.db.models import Func, F

Model.objects.annotate(abs_balance=Func(F('balance'), function='ABS')).order_by('-abs_balance')

edited Jun 16 '18 at 05:42

answered Jun 16 '18 at 05:36

neverwalkaloner

46,181
7
92
100

This throws `1054, "Unknown column '17' in 'order clause'"`. I'm guessing it's because I'm forming my queryset by a union of two different querysets from the same model - but there is a missing value for the column I'm performing the `order_by` on. ? – Bill Armstrong Jun 16 '18 at 06:01
@BillArmstrong yep, if second querysent doesn't have annotated `abs_balance` value you will get error. – neverwalkaloner Jun 16 '18 at 06:03
1

yep - I can get it to work on the separate querysets, just need to get the union or similar to work together - thanks. – Bill Armstrong Jun 16 '18 at 06:18
1

One final comment... To get the order from largest `ABS` value to smallest, use a `-` in the attribute. (e.g., `.order_by('-balance')` ) – Bill Armstrong Jun 16 '18 at 06:25
Regarding a `.union()` that is then subjected to an `.annotate()` - it current does not work, see this [bug ticket](https://code.djangoproject.com/ticket/26019#no1). – Bill Armstrong Jun 16 '18 at 06:56
@neverwalkaloner this fails for me when I try `.annotate( distance=Func(F('datetime') - datetime_goal, function='ABS') ).order_by( 'distance' )` with `function abs(interval) does not exist` – Vidak Jul 11 '18 at 14:57
1

@Vidak Check this question https://stackoverflow.com/questions/12460797/sort-timestamps-including-future-by-absolute-distance-from-now Try to use expressionwrapper to cast output_type for substraction result: https://docs.djangoproject.com/en/2.0/ref/models/expressions/#django.db.models.ExpressionWrapper – neverwalkaloner Jul 11 '18 at 15:11
1

@neverwalkaloner I solved the problem using the question you linked, by using case/when rather than expressionwrapper. I made a separate answer, if you'd like you can add the solution with expressionwrapper, I coulndn't figure it out :D Thanks a lot btw! – Vidak Jul 11 '18 at 16:07

score 7 · Answer 2 · answered Jul 11 '18 at 16:05

Answer by @neverwalkaloner is excellent, but doesn't work when dealing with datetime objects, e.g. if you want to order objects by distance to some specific timestamp.

For example:

queryset.annotate(
    distance=Func(F('datetime') - datetime_goal, function='ABS')
).order_by('distance')

fails with ProgrammingError: function abs(interval) does not exist, which is explained in this question.

In this case, you can use a Case expression to simulate absolute value, like this:

from django.db.models import Case, When
queryset.annotate(
    distance=Case(
        When(datetime__gte=datetime_goal, then=F('datetime')-datetime_goal),
        When(datetime__lt=datetime_goal, then=datetime_goal-F('datetime')),
    )
).order_by('distance')

Django queryset order_by absolute value

2 Answers2