Creating a QuerySet manually in Django from list of ids

Question

Let's say I have a model My_model and I have a list of ids for this model;

my_ids = [3,2,1,42]

Now I want to get a QuerySet with the My_model.objects of the ids. I already found this solution, but it doesn't work for me, because I want to have the objects in the QuerySet in the same order as the ids in my_ids.

How can I turn a list of ids in a QuerySet in Django and Python?

maybe [this](https://stackoverflow.com/a/45693631/13168118) answers your question? — bb4L, May 08 '20 at 19:25
No, `in_bulk()` doesn't care about the ordering of what you pass it. — Tom Carrick, May 08 '20 at 19:26
@Sven basicaully you need [this](https://stackoverflow.com/questions/3397437/manually-create-a-django-queryset-or-rather-manually-add-objects-to-a-queryset) and add order as suggest, for example, [here](https://stackoverflow.com/questions/866465/order-by-the-in-value-list) — Konstantin, May 08 '20 at 19:35

Willem Van Onsem · Accepted Answer · 2020-05-08T19:51:05.810

6

It indeed does not care about the order, since the query looks like:

SELECT model.*
FROM model
WHERE model.pk IN (3, 2, 1, 42)

The database can return the values in any order it wants. Depending on the database you use, the indexing mechanism, etc. the order can be "deterministic", but it is even possible that it will return records completely random.

You can order these, with a rather complicates expression:

from django.db.models import Case, IntegerField, Value, When

my_ids = [3,2,1,42]

Model.objects.filter(
    pk__in=my_ids
).order_by(
    Case(
        *[When(pk=pk, then=Value(i)) for i, pk in enumerate(my_ids)],
        output_field=IntegerField()
    ).asc()
)

This is still a QuerySet, and can be used for lazy querying purposes, but the question remains why you want to sort the elements based on the order of the primary keys. Usually the model (or a related model) contains more "interesting" ways to order elements.

edited May 08 '20 at 19:51

answered May 08 '20 at 19:36

Willem Van Onsem

443,496
30
428
555

Excuse me if this question is very dumb, but is that efficient? – Sven May 08 '20 at 19:40
1

@Sven: the ordering is done at the database side, which tends to be a bit more efficient. It is at least more efficient than querying one-by-one. Whether it is more efficient can depend on several aspects of the database. Finally it is lazy, so if you somehow do not use the queryset, then it is never even queries on the database. – Willem Van Onsem May 08 '20 at 19:42
1

@Sven: we make an expression that at the database says that the "score" for the item with `pk=3` is `0`, for `pk=2` is `1`, for `pk=1` is `2`, and for `pk=42` is `3`, then we order by that score. – Willem Van Onsem May 08 '20 at 19:43
1

@Sven, as long as you do not "consume" a queryset (for example not iterate over it, or call `len(..)`, `.count()`, etc., it will not make a query to the database at all). A `QuerySet` is basically a "promise" that it *will* execute the query when necessary. – Willem Van Onsem May 08 '20 at 19:47
1

@Sven: I forgot a comma at the end :) – Willem Van Onsem May 08 '20 at 19:51
Ah I see, ok then thanks for your help really appreciate it!! – Sven May 08 '20 at 19:51

Tom Carrick · Answer 2 · 2020-05-08T19:42:00.187

You can use in_bulk():

queryset = Foo.objects.in_bulk(my_ids)

However, this will have the same problem. This isn't a limitation of Django, but of SQL. The database will give you back the data however it wants unless you explicitly order it, and there's not really a good way to arbitrarily order things.

So the answer is, you can't, easily. You could possibly do weird things with CASE in SQL, but this will possibly be slow.

Probably the simplest way is to convert the queryset to a list and then do your custom ordering in Python, something like:

objs = sorted(list(queryset), key=lambda x: my_ids.index(x.id))

Edit: Willem's answer does weird things with case that is probably faster than this, but I still wouldn't want to grab stuff in arbitrary orders if you care about speed.

Creating a QuerySet manually in Django from list of ids

2 Answers2

Linked