CODEWITHSUNDEEP
pythondjangopython-3.x
Bill Armstrong
Bill Armstrong

Reputation: 1777

Django queryset order_by absolute value

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?

Upvotes: 6

Views: 3456

Answers (2)

Vidak
Vidak

Reputation: 1103

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

Upvotes: 8

neverwalkaloner
neverwalkaloner

Reputation: 47354

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

Upvotes: 13

Related Questions