[Answered ]-How can I order a Django query against a custom list of values?

by

2👍

✅

It looks like you have rank stored in a CharField in database. It’s not simple to do a custom order_by without changing the schema. Therefore, for simple cases, you may consider to make the ordering in python code: 

rank_lookup = {rank: n for n,(rank,rank) in enumerate(rank_choices[::-1])}
ranks = Rank.objects.filter(...).values_list('rank', flat=1)
ranks = sorted(ranks, key=rank_lookup.get)

After the call to sorted, the queryset will be evaluated and you will have instead a python list.

If this is not satisfactory, it is possible (but not pretty) in the Django ORM to get the result you want in a queryset by using a Case/When construct:

>>> for rank, rank in rank_choices:
...     Rank.objects.create(rank=rank)
...     
>>> Rank.objects.order_by('rank')  # alphabetical
[<Rank: Baron>, <Rank: Duke>, <Rank: King>, <Rank: Lackey>, <Rank: Prince>]
>>> rank_lookup = {rank: n for n,(rank,rank) in enumerate(rank_choices)}
>>> cases = [When(rank=rank, then=Value(rank_lookup[rank])) for rank in rank_lookup]
>>> cases
[<When: WHEN <Q: (AND: ('rank', 'King'))> THEN Value(0)>,
 <When: WHEN <Q: (AND: ('rank', 'Lackey'))> THEN Value(4)>,
 <When: WHEN <Q: (AND: ('rank', 'Baron'))> THEN Value(3)>,
 <When: WHEN <Q: (AND: ('rank', 'Prince'))> THEN Value(1)>,
 <When: WHEN <Q: (AND: ('rank', 'Duke'))> THEN Value(2)>]
>>>
>>> Rank.objects.annotate(my_order=Case(*cases, output_field=IntegerField())).order_by('my_order')
[<Rank: King>, <Rank: Prince>, <Rank: Duke>, <Rank: Baron>, <Rank: Lackey>]
>>> Rank.objects.annotate(my_order=Case(*cases, output_field=IntegerField())).order_by('-my_order')
[<Rank: Lackey>, <Rank: Baron>, <Rank: Duke>, <Rank: Prince>, <Rank: King>]

Thanks to user “mu is too short” in the comments for inspiring this idea. This will work in Django 1.8+ because of the new features in conditional expressions.

For users unfortunate enough to be stuck on older versions of Django, the same idea is possible by constructing a raw sql fragment to pass in using Queryset.extra.

👤wim

Leave a comment