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.

https://docs.djangoproject.com/en/4.1/ref/models/conditional-expressions/#case — NixonSparrow, Sep 12 '22 at 07:41
This will actually order in the opposite order, since `False` is less than `True`. — Willem Van Onsem, Sep 12 '22 at 07:49

score 3 · Accepted Answer · answered Sep 12 '22 at 07:51

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.

Harun Yilmaz · Answer 2 · 2022-09-12T08:05:39.637

2

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

edited Sep 12 '22 at 08:05

answered Sep 12 '22 at 07:39

Harun Yilmaz

8,281
3
24
35

1

Thanks, that is nice and easy to understandhowever it just needs `order_by('n_order', 'n')` as per @Willem's answer below. – run_the_race Sep 12 '22 at 08:00
Yeah you're absolutely right! Updated the answer accordingly – Harun Yilmaz Sep 12 '22 at 08:02
2

@HarunYilmaz: another small detail is that `.order_by(..)` orders from small-to-large by default, hence the `Value(3)`, etc. will put `n=7` last and `n=17` second to last. You should use `-custom_order`, or reverse the values. – Willem Van Onsem Sep 12 '22 at 08:04
@WillemVanOnsem I was just adding a comment on your answer to see if it's in a reverse order haha :D You're right, too. Thanks – Harun Yilmaz Sep 12 '22 at 08:06

Django ORM: Perform conditional `order_by`

2 Answers2

Linked