Sort queryset by values in list

Question

Is it possible to sort a django queryset by the list of elements provided in the query? For example, if I do

m.objects.filter(id__in=[3,1,8])

I wan't the order of the queryset to be the element of id 3, the element of id 1 and the element of id 8.

Thanks

Answer 1

Since there exists Case and When in Django >= 1.11 you can do it in a more orm-like way keeping all the benefits of your queryset:

from django.db import models

order = ['b', 'a', 'z', 'x', 'c']
_whens = []

for sort_index, value in enumerate(order):
    _whens.append(
        models.When(my_field=value, then=sort_index)
    )

qs = MyModel.objects.annotate(
        _sort_index=models.Case(
            *_whens, 
            output_field=models.IntegerField()
        )
     )

qs.order_by('_sort_index')

This will generate something like this:

from django.db import models

order = ['b', 'a', 'z', 'x', 'c']
qs = MyModel.objects.annotate(
    _sort_index=models.Case(
        models.When(my_field='b', then=0),
        models.When(my_field='a', then=1),
        models.When(my_field='z', then=2),
        models.When(my_field='x', then=3),
        models.When(my_field='c', then=4),
        output_field=models.IntegerField()
    )
).order_by('_sort_index')

I would suggest to use this only with a small amount of list-items because it blows up the database-query.

https://docs.djangoproject.com/ko/1.11/ref/models/conditional-expressions/#case

Answer 2

_list = [3,1,2]
sorted(queryset, key=lambda x: _list.index(x.id)) 

Answer 3

No. There is no way to do what you want short of some contrived mechanism like:

qs = m.objects.filter(id__in=[3,1,8])
qs_sorted = list()
for id in [3,1,8]:
    qs_sorted.append(qs.get(id=id))

You'd need to throw some exception handling in there as well, just in case one of the specified ids wasn't actually returned.

The negatives are that this will effectively negate the lazy loading of queryset. In fact, it's very likely to generate multiple DB queries (haven't tested that though). Also, you end up with a normal python list instead of a queryset, so no more filtering is possible.

Answer 4

See Ordering by the order of values in a SQL IN() clause - you'd need to drop down to a raw queryset and it'd be database specific.

If you're using MySQL for example you should be able to do a

Model.objects.raw('select * from myapp_model where id in (3,1,8) order by field(id,3,1,8)')

Answer 5

Most of your sorting options assume there is an order to what you are sorting. If you can define the order you can use order_by and a field on your model, or sorted using python. If you want to just reorder things (with seemingly no logic) you could still probably use sorted:

for id in [3,1,8]:
    sorted(qs, key=lambda x: x.id == id)

Answer 6

Maybe you can use order-by as zeekay mentioned.

If it is OK that your QuerSet will be turned into a list, then you can use something like this

sorted(queryset, key=lambda x: x.id) 

Answer 7

Thank you all for your answers. I'v came up with another solution. It's not the best one but is only needs to do one query to the database. I have a list with the id's I want ordered by my needs. Then I make the query and fill in a dictionary with the data and finally I traverse l picking up the objects in an ordered fashion.

l =[7,1,4]
p_dict = {}
p = Poi.objects.filter(id__in=l)
for i in p:
    p_dict[i.id] = i

Using this I will use a lot of memory right? But I gain in accesses to the database.

@a-lee I will investigate your solution and try to tranform it to postgresql.

Answer 8

preserved = [Case(*[When(pk=pk, then=pos) for pos, pk in enumerate([3,1,8])])]
queryset.order_by(*preserved)