Django: __in query lookup doesn't maintain the order in queryset

Question

I have ID's in a specific order

>>> album_ids = [24, 15, 25, 19, 11, 26, 27, 28]
>>> albums = Album.objects.filter( id__in=album_ids, published= True )
>>> [album.id for album in albums]
[25, 24, 27, 28, 26, 11, 15, 19]

I need albums in queryset in the same order as id's in album_ids. Anyone please tell me how can i maintain the order? or obtain the albums as in album_ids?

Does this answer your question? [Django get a QuerySet from array of id's in specific order](https://stackoverflow.com/questions/4916851/django-get-a-queryset-from-array-of-ids-in-specific-order) — adnanmuttaleb, Aug 26 '20 at 07:32

score 22 · Accepted Answer · answered Sep 09 '11 at 12:26

22

Assuming the list of IDs isn't too large, you could convert the QS to a list and sort it in Python:

album_list = list(albums)
album_list.sort(key=lambda album: album_ids.index(album.id))

answered Sep 09 '11 at 12:26

Daniel Roseman

588,541
66
880
895

1

I have large database, ID's can be in millions – Ahsan Sep 09 '11 at 12:35
Just for future reference, I think the fact that the ID's can be millions is not relevant in most applications because we rarely need to load such an amount of information. Instead, you can use django-pagination which loads only the amount needed to display and makes for a better user experience. – r_31415 Aug 26 '12 at 04:16
1

This is a nice solution, but how to do it keeping the QuerySet to follow working on it? – Jose Luis de la Rosa Dec 09 '14 at 02:45
1

I'm still trying to grok this, but if you happen to need to keep the QuerySet, say you're passing it to a ModelForm in the Admin, this post seems to have the answer: http://blog.mathieu-leplatre.info/django-create-a-queryset-from-a-list-preserving-order.html. In my case I need to order a set of genericrelations, based on the content_objects. – Hylidan Feb 13 '15 at 00:29
Fails if an object failed to be retrieved (i.e it doesn't exist anymore) – madprops Sep 12 '16 at 12:41

alTus · Answer 2 · 2011-09-09T19:46:46.823

10

You can't do it in django via ORM. But it's quite simple to implement by youself:

album_ids = [24, 15, 25, 19, 11, 26, 27, 28]
albums = Album.objects.filter(published=True).in_bulk(album_ids) # this gives us a dict by ID
sorted_albums = [albums[id] for id in albums_ids if id in albums]

edited Sep 09 '11 at 19:46

answered Sep 09 '11 at 12:26

alTus

2,177
1
14
23

score 4 · Answer 3 · answered Jan 24 '22 at 07:15

4

For Django versions >= 1.8, use below code:

from django.db.models import Case, When

field_list = [8, 3, 6, 4]
preserved = Case(*[When(field=field, then=position) for position, field in enumerate(field_list)])
queryset = MyModel.objects.filter(field__in=field_list).order_by(preserved)

Here is the PostgreSQL query representation at database level:

SELECT *
FROM MyModel
ORDER BY
  CASE
    WHEN id=8 THEN 0
    WHEN id=3 THEN 1
    WHEN id=6 THEN 2
    WHEN id=4 THEN 3
  END;

answered Jan 24 '22 at 07:15

Rashid Mahmood

313
4
10

this is a beautiful solution. deserves more clout – Hammad Sep 01 '22 at 09:09
should be accepted answer. only thing that works on scale for me – Saša Kalaba Jan 11 '23 at 16:58

score 1 · Answer 4 · answered Feb 06 '18 at 17:29

1

You can do it in Django via ORM using the extra QuerySet modifier

>>> album_ids = [24, 15, 25, 19, 11, 26, 27, 28]
>>> albums = Album.objects.filter( id__in=album_ids, published= True
             ).extra(select={'manual': 'FIELD(id,%s)' % ','.join(map(str, album_ids))},
                     order_by=['manual'])

answered Feb 06 '18 at 17:29

Harish

425
7
22

Please note that if this solution is used with string data, it is not safe for sql injection. – Barney Szabolcs Apr 26 '21 at 09:46

score 1 · Answer 5 · edited Apr 26 '21 at 12:07

Using @Soitje 's solution: https://stackoverflow.com/a/37648265/1031191

def filter__in_preserve(queryset: QuerySet, field: str, values: list) -> QuerySet:
    """
    .filter(field__in=values), preserves order.
    """
    # (There are not going to be missing cases, so default=len(values) is unnecessary)
    preserved = Case(*[When(**{field: val}, then=pos) for pos, val in enumerate(values)])
    return queryset.filter(**{f'{field}__in': values}).order_by(preserved)


album_ids = [24, 15, 25, 19, 11, 26, 27, 28]
albums =filter__in_preserve(album.objects, 'id', album_ids).all()

Note that you need to make sure that album_ids are unique.

Remarks:

1.) This solution should safely work with any other fields, without risking an sql injection attack.

2.) Case (Django doc) generates an sql query like https://stackoverflow.com/a/33753187/1031191

order by case id 
          when 24 then 0
          when 15 then 1
          ...
          else 8 
end

score 0 · Answer 6 · answered Jan 26 '20 at 04:43

If you use MySQL and want to preserve the order by using a string column.

words = ['I', 'am', 'a', 'human']
ordering = 'FIELD(`word`, %s)' % ','.join(str('%s') for word in words)
queryset = ModelObejectWord.objects.filter(word__in=tuple(words)).extra(
                            select={'ordering': ordering}, select_params=words, order_by=('ordering',))

Django: __in query lookup doesn't maintain the order in queryset

6 Answers6

Linked

Related