Django ORM: Perform conditional `order_by`

run_the_race 0
Viewed 40

Say one has this simple model:

from django.db import models

class Foo(models.Model):
    n = models.IntegerField()

In SQL you can perform an order by with a condition e.g.

select * from foo orber by n=7, n=17, n=3, n

This will sort the rows by first if n is 7, then if n is 14, then if n is 3, and then finally by n ascending.

How does one do the same with the Django ORM? It is not covered in their order_by docs.

django-orm django-models django
2 Answers

You can use .annotate() to assign records a custom_order value and use then .order_by() to order the queryset based on this value.

For example:

Foo.objects \
.annotate(custom_order=Case( 
    When(n=7, then=Value(0)), 
    When(n=17, then=Value(1)), 
    When(n=3, then=Value(2)),
    default=Value(3),
    output_field=IntegerField()
 ) \
.order_by('custom_order', 'n')
Harun Yilmaz0

You can work with a generic solution that looks like:

from django.db.models import Case, IntegerField, When, Value

items = [7, 17, 3]
Foo.objects.alias(
    n_order=Case(
        *[When(n=item, then=Value(i)) for i, item in enumerate(items)],
        default=Value(len(items)),
        output_field=IntegerField()
    )
).order_by('n_order', 'n')

This thus constructs a conditional expression chain [Django-doc] that is used first, and if n is not one of these, it will fall back on ordering with n itself.

Willem Van Onsem0
Related