[Django]-Django order_by specific order

by

5👍

✅

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.

👤okm

57👍

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

26👍

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)
👤Hiroki

0👍

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.

👤0urz4g

Leave a comment