How do I sort a Django QuerySet by an external value?

Question

I have a dict made up of (id, rank) pairs. I'd like to perform a Django query on the ids such that the resultant queryset is ordered by rank (descending).

Getting the queryset is easy:

rankings = {...}
result = MyModel.objects.filter(id__in=rankings.keys())

It seems like the answer should involve some sort of annotation that I can use as part of the order_by but I can't figure out how to get there.

EDIT: I neglected to mention that I need the result to be a QuerySet as this is part of a tastypie API pipeline.

I don't think you can do it. How about storing those values in a field? — dannyroa, Oct 23 '12 at 22:14
The ranks change on each query. The application is looking at a set of schools and ranking them for a user based on expressed preferences. — Ron Craswell, Oct 23 '12 at 22:22
My suggestion is that you add a table to store the rankings in, and then do a join. This would allow you to do something like `MyModel.objects.order_by(...)` based ln the value of the joined table/field. — Jack Westmore, Oct 27 '20 at 04:09

score 8 · Answer 1 · answered Apr 12 '19 at 11:21

8

My solution for Django > 1.10 and PostgreSQL > 9.5

from django.db.models import Func, Value, IntegerField, CharField
from django.contrib.postgres.fields import ArrayField


class ArrayPosition(Func):
    function = 'array_position'

    def __init__(self, items, *expressions, **extra):
        if isinstance(items[0], int):
            base_field = IntegerField()
        else:
            base_field = CharField(max_length=max(len(i) for i in items))
        first_arg = Value(list(items), output_field=ArrayField(base_field))
        expressions = (first_arg, ) + expressions
        super().__init__(*expressions, **extra)


pk_list = [234,12,23]
queryset = SomeModel.objects.filter(pk__in=pk_list, ...)\
    .annotate(ordering=ArrayPosition(pk_list, F('pk'), output_field=IntegerField()))\
    .order_by('ordering')

answered Apr 12 '19 at 11:21

Rinaatt

81
1
2

Awesome solution, thanks! This function should be in [`django.contrib.postgre.functions`](https://docs.djangoproject.com/en/dev/ref/contrib/postgres/functions/). – Benoit Blanchon Nov 20 '20 at 15:56
Since the output field is always an integer, I think you can safely define `output_field` as a class variable, like they do in [django/contrib/postgres/functions.py](https://github.com/django/django/blob/b9cf764be62e77b4777b3a75ec256f6209a57671/django/contrib/postgres/functions.py) – Benoit Blanchon Nov 20 '20 at 16:03
You can find an [alternative implementation of `ArrayPosition`](https://github.com/primal100/django_postgres_extensions/blob/bb1edc2cbf194fe571a605595a898b2528918301/django_postgres_extensions/models/functions.py#L50) in the (now retired) package [django_postgres_extensions](https://github.com/primal100/django_postgres_extensions). – Benoit Blanchon Nov 20 '20 at 16:54
Good solution! I found an even simpler in the first answer here: https://stackoverflow.com/questions/4916851/django-get-a-queryset-from-array-of-ids-in-specific-order – larsjr Dec 01 '21 at 13:36

Timmy O'Mahony · Answer 2 · 2012-10-23T22:34:40.060

1

Something like this?

rankings = { 1 : 2, 2: 1, ... } # i.e. { 'id' : 'ranking', ... }
objects = list(MyModel.objects.filter(id__in=rankings.keys()))
objects.sort(key=lambda obj: rankings[obj.id])

edited Oct 23 '12 at 22:34

answered Oct 23 '12 at 22:24

Timmy O'Mahony

53,000
18
155
177

1

Interesting solution. How expensive is the sort? – dannyroa Oct 23 '12 at 22:26
That's the idea, but there's no "sort" method on a QuerySet so I don't think this code works. – Ron Craswell Oct 23 '12 at 22:32
I've edited the code - you can convert the QS to a `List` but it will mean evaluating the QS immediately. I'm not sure of the efficiency of the sorting, but I'm not sure if there is an alternative solution (certainly not involving the `Queryset` API as far as I'm aware) – Timmy O'Mahony Oct 23 '12 at 22:35
1

I think this works but might be an issue if you're using Paginator. – dannyroa Oct 23 '12 at 22:40
I didn't mention (but have edited the original question to reflect) that I need the result to be a QuerySet. – Ron Craswell Oct 24 '12 at 00:38

score 0 · Accepted Answer · answered Oct 24 '12 at 01:45

The only way I could figure out to solve this is to create a new ranking model related to primary model. On each query I insert the ranking items into that model and then am able to execute the order_by via the relation. (Using annotate to add the rank to the school records.)

class UserSchoolRanking(models.Model):
    user = models.ForeignKey(User)
    school = models.ForeignKey(School)
    rank = models.IntegerField()

bulk_user_school_rank = [UserSchoolRank(user=user, school_id=k, rank=v)
                                         for k, v in rankings.iteritems()]
UserSchoolRank.objects.bulk_create(bulk_user_school_rank)

schools = School.objects.filter(userschoolrank__user=user)\
                .annotate(rank=Min('userschoolrank__rank'))\
                .order_by('-userschoolrank__rank')

How do I sort a Django QuerySet by an external value?

3 Answers3

Linked