How can I order a Django query against a custom list of values?

Question

I'm populating a dropdown with values defined in my models.py, something like:

    rank_choices = (
        ('King', 'King')
        ('Prince', 'Prince')
        ('Duke', 'Duke')
        ('Baron', 'Baron')
        ('Lackey', 'Lackey')

When I'm displaying a list of people with a rank at a particular time, I want them to appear in descending rank.

Current query (merely alphabetic):

attendees = Rank.objects.filter(feast__id=feast__id).exclude(rank='Lackey').order_by('rank')

How could I change this to order rank by its position in the list?

I'm considering this:

    rank_choices = (
        (0, 'King')
        (1, 'Prince')
        (2, 'Duke')
        (3, 'Baron')
        (4, 'Lackey')

But even if I can get the verbose values back from the numeric values, any changes in the ordering would return incorrect values for data pre-change. Is there a better way?

Chosen solution

Inspired by wim's answer, I ended up doing a data migration to change the ranks to numeric values and sorting as follows.

ranks = sorted(ranks, key=lambda x: int(x.rank))

I'm getting the verbose values back by importing the rank_choices from my models into my views and replacing the numeric values with the corresponding titles after the sorting.

Community · Accepted Answer · 2017-05-23 11:45:31Z

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Apr 18, 2016 at 20:41

wim

369k114 gold badges682 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Wilco van Esch Over a year ago

Thanks. I'm going to try this. I will need to display some other attributes of the Rank object and its fk objects, but maybe I can leave out the flat=1, give multiple columns to values_list and work with that.

wim Over a year ago

Yep, just modify the argument passed to key in the sorted call

mu is too short Over a year ago

Can't you specify an ORDER BY expression with Django? order by case rank when 'King' then 0 when 'Prince' then 1 ... end for example. SQL lets you order by any valid expression, not just columns.

wim Over a year ago

@muistooshort I honestly don't know. If you can get it working, post an answer :)

mu is too short Over a year ago

I know SQL but very little Django. The order_by('rank') in the OP's code suggests that order_by is happy with a string argument so maybe order_by('case rank when ...') would work. Alternatively, you could JOIN to a two column table with rank names and numbers and order by the JOINed number, that would probably be cleaner inside Django and require less string wrangling and escaping.

|

Collectives™ on Stack Overflow

How can I order a Django query against a custom list of values?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related