Problem with getting model instances in specific order

Question

I have a list of passport data of users. I need to get users queryset in the same sequence as their passport data ordered in the list. Here is the list with passport data:

lst = ['AA1111111', 'AD2222222', 'AA3333333', 'AA4444444', 'АВ5555555']

I tried to do something like this:

empty_queryset = Users.objects.none()
for passport_number in lst:
    user = Users.objects.filter(passport_number__exact=passport_number)
    empty_queryset |= user

I was expecting this:

<QuerySet [<Users: AA1111111>, <Users: AD2222222>, <Users: AA3333333>, <Users: AA4444444>, <Users: АВ5555555>]>

But it came in chaotic order:

<QuerySet [<Users: АВ5555555>, <Users: AA1111111>, <Users: AD2222222>, <Users: AA3333333>, <Users: AA4444444>]>

Then I tried this:

Users.objects.filter(passport_number__in=[i for i in lst])

But still did not work

Answer 1

Both essentially do the same, making a query that looks like:

SELECT *
FROM users
WHERE passport_number IN ('AA1111111',
                          'AD2222222',
                          'AA3333333',
                          'AA4444444',
                          'АВ5555555')

since there is no ORDER BY clause, the database can present the data in any order it wants. Typically if indexes are involved it can be ordered like the index, but that is more an implementation detail.

You can work with .union(…) ^[Django-doc] to work with:

SELECT *
FROM users
WHERE passport_number = 'AA1111111'
UNION
SELECT *
FROM users
WHERE passport_number = 'AD2222222'
-- …

then it looks like:

Users.objects.none().union(
    *[User.objects.filter(passport_number=p) for p in lst], all=True
)

Another option would be to sort the items at the Django/Python level with:

lst = ['AA1111111', 'AD2222222', 'AA3333333', 'AA4444444', 'АВ5555555']
order = {k: i for i, k in enumerate(lst)}

items = sorted(
    Users.objects.filter(passport_number__in=lst),
    key=lambda x: order.get(x.passport_number),
)

but then it thus is a list of Users, not a QuerySet, so additional filtering, pagination, etc. can then not be done through the QuerySet API.

---

Note: Normally a Django model is given a singular name ^{[django-antipatterns]}, so User instead of Users.