Django ORM: Perform conditional `order_by`

Question

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.

Answer 1

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.