111

heres a quick one for you:

I have a list of id's which I want to use to return a QuerySet(or array if need be), but I want to maintain that order.

Thanks

neolaser
  • 6,722
  • 18
  • 57
  • 90

6 Answers6

289

Since Django 1.8, you can do:

from django.db.models import Case, When

pk_list = [10, 2, 1]
preserved = Case(*[When(pk=pk, then=pos) for pos, pk in enumerate(pk_list)])
queryset = MyModel.objects.filter(pk__in=pk_list).order_by(preserved)
Soitje
  • 2,994
  • 2
  • 10
  • 7
  • 26
    Best answer because it actually returns a queryset – Teebes Feb 14 '17 at 19:24
  • 2
    I still think that django's `Case` `When` are underrated! – Babu May 09 '17 at 11:26
  • I'm going to use `distinct()` with order_by case when clause but got the error. any support, please. – elquimista May 31 '17 at 18:28
  • `SELECT DISTINCT ON expressions must match initial ORDER BY expressions` - here is the error message – elquimista May 31 '17 at 18:29
  • 3
    I strongly recommend this is the best answer! – Tony Nov 10 '17 at 07:02
  • great answer. I really wanted a solution that returns a queryset and found this answer after a long search. Brilliant @Soitje – Anupam Jan 20 '18 at 13:10
  • How to do a descending on preserved as in my case pk_list is a list of strings like ['Python', 'PHP', 'Java', '.Net', 'Nodejs'] and so preserved becomes: preserved = Case(*[When(agent_type=agent_type, then=pos) for pos, agent_type in enumerate(od)]) and as a result theses ordered value ends up being in last – vijay shanker May 24 '18 at 13:29
  • is it optimized for pagination also? meaning does it process all the results(I want to use `Paginator`, hence do not want all the results to be fetched)? – Vikas Goyal Aug 24 '18 at 07:38
  • What happens when there's no object with a given ID? What should I do if I want an error? What should I do if I just want `None`? – Boris Verkhovskiy Jan 31 '20 at 21:04
  • 2
    This should have been the selected answer. – Subangkar KrS Aug 09 '20 at 18:31
  • This should not be the selected answer, at least should not go without a warning about the performance. This way it does not perform well, neither on small querysets nor on big ones. sorting the result using plain python list, as in other answers performs much faster and should be considered on small QSs. On big ones you can't do it this way at all. – Pavel Shishmarev Jun 06 '22 at 12:24
  • My hero <3. I've spent many hours at last few days searching for a solution like this. – Victor Fernandes Aug 22 '22 at 15:00
76

I don't think you can enforce that particular order on the database level, so you need to do it in python instead.

id_list = [1, 5, 7]
objects = Foo.objects.filter(id__in=id_list)

objects = dict([(obj.id, obj) for obj in objects])
sorted_objects = [objects[id] for id in id_list]

This builds up a dictionary of the objects with their id as key, so they can be retrieved easily when building up the sorted list.

Reiner Gerecke
  • 11,936
  • 1
  • 49
  • 41
30

If you want to do this using in_bulk, you actually need to merge the two answers above:

id_list = [1, 5, 7]
objects = Foo.objects.in_bulk(id_list)
sorted_objects = [objects[id] for id in id_list]

Otherwise the result will be a dictionary rather than a specifically ordered list.

Rick Westera
  • 3,142
  • 1
  • 35
  • 23
30

Here's a way to do it at database level. Copy paste from: blog.mathieu-leplatre.info :

MySQL:

SELECT *
FROM theme
ORDER BY FIELD(`id`, 10, 2, 1);

Same with Django:

pk_list = [10, 2, 1]
ordering = 'FIELD(`id`, %s)' % ','.join(str(id) for id in pk_list)
queryset = Theme.objects.filter(pk__in=[pk_list]).extra(
           select={'ordering': ordering}, order_by=('ordering',))

PostgreSQL: 

SELECT *
FROM theme
ORDER BY
  CASE
    WHEN id=10 THEN 0
    WHEN id=2 THEN 1
    WHEN id=1 THEN 2
  END;

Same with Django: 

pk_list = [10, 2, 1]
clauses = ' '.join(['WHEN id=%s THEN %s' % (pk, i) for i, pk in enumerate(pk_list)])
ordering = 'CASE %s END' % clauses
queryset = Theme.objects.filter(pk__in=pk_list).extra(
           select={'ordering': ordering}, order_by=('ordering',))
user
  • 17,781
  • 20
  • 98
  • 124
12
id_list = [1, 5, 7]
objects = Foo.objects.filter(id__in=id_list)
sorted(objects, key=lambda i: id_list.index(i.pk))
Andrew G
  • 817
  • 1
  • 9
  • 13
0

Another better/cleaner approach can be

pk_list = [10, 2, 1]
sorted_key_object_pair = MyModel.objects.in_bulk(pk_list)
sorted_objects = sorted_key_object_pair.values()

Simple, clean, less code.

A.J.
  • 8,557
  • 11
  • 61
  • 89