How to sort Django QuerySet by external value?

Question

I have a pointer consisting of pairs (id, rank). I would like to execute a Django query for identifiers so that the result set of queries is sorted by rank (descending).

Getting a request is easy:

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

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

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

Answer 1

The only way to solve this problem is to create a new rating model related to the primary model. For each query, I insert ranking elements into this model, and then I can execute order_by through the relation. (Using annotations to add rank to 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') 

Answer 2

Something like that?

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

Answer 3

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