Django order_by specific order

Question

Is it possible to replicate this kind of specific sql ordering in the django ORM:

order by

(case

    when id = 5 then 1

    when id = 2 then 2

    when id = 3 then 3

    when id = 1 then 4

    when id = 4 then 5

end) asc

?

Edge

Answer 1

You could do it w/ extra() or more plain raw(), but they can not work well w/ more complex situation.

qs.extra(select={'o':'(case when id=5 then 1 when id=2 then 2 when id=3 then 3 when id=1 then 4 when id=4 then 5 end)', order_by='o'}

YourModel.raw('select ... order by (case ...)')

For your code, condition set is very limited, you could sort in Python easily.

Urethrectomy

Answer 2

Since Django 1.8 you have Conditional Expressions so using extra is not necessary anymore.

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

SomeModel.objects.annotate(
    custom_order=Case(
        When(id=5, then=Value(1)),
        When(id=2, then=Value(2)),
        When(id=3, then=Value(3)),
        When(id=1, then=Value(4)),
        When(id=4, then=Value(5)),
        output_field=IntegerField(),
    )
).order_by('custom_order')

Alitaalitha

What is the difference between using annotate and then order_by, instead of directly setting the Case in order_by? – Derinna

@guival same issue for me, have an answer yet? – Youngs

@Youngs I'm still not sure about the difference, I decided to simply put the Case inside the order_by, without doing the annotate, and it works. – Derinna

I've find this way being very useful also when combining the custom_order with additional ordering from other fields in the model, e.g. .order_by('custom_order', '-created_at') – Allrud

The part that helped me here is the output_field. Django keeps changing how the inferrence works so it's best to just always include an output field. – Regnal

Answer 3

It is possible. Since Django 1.8 you can do in the following way:

from django.db.models import Case, When

ids = [5, 2, 3, 1, 4]
order = Case(*[When(id=id, then=pos) for pos, id in enumerate(ids)])
queryset = MyModel.objects.filter(id__in=ids).order_by(order)

Gunny

I can't believe that worked. I have no idea how but that's SO programming for ya. – Weasner

@Gunny amazing! – Rodneyrodolfo

Answer 4

I answer to this old question for sharing an evolution of Django

Since Django 3.2, there is alias() very well suited for this case :

Same as annotate(), but instead of annotating objects in the QuerySet, saves the expression for later reuse with other QuerySet methods. This is useful when the result of the expression itself is not needed but it is used for filtering, ordering, or as a part of a complex expression. Not selecting the unused value removes redundant work from the database which should result in better performance.

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

class MyObject(models.Model):
    priority = models.CharField(max_length=10)

priority_order = Case(
    When(priority='high', then=Value(1)),
    When(priority='medium', then=Value(2)),
    When(priority='low', then=Value(3)),
)
MyObject.objects.alias(priority_order=priority_order).order_by("priority_order")

Based on the Django documentation, it seems that alias() is better than annotate() or a direct expression in order_by() :

filter() and order_by() can take expressions directly, but expression construction and usage often does not happen in the same place (for example, QuerySet method creates expressions, for later use in views). alias() allows building complex expressions incrementally, possibly spanning multiple methods and modules, refer to the expression parts by their aliases and only use annotate() for the final result.

Ldopa