Fastest way to fetch multiple rows by ID, with ordering intact

Question

I need to do a lookup and info from the Product table based on an order list of primary keys.

pks = [22,51,22,45]
products = list(Products.object.filter(pk__in=pks).values_list("pk", flat=True))
# products order is not same as pks's order.


# one solution is to put them into a mapping of sorts
products_by_id = {
prod.pk : prod for prod in Products.object.filter(pk__in=pks)
}
ordered_prods = [products_by_id[pk] for pk in pks]

Is there a better or faster way of doing that with the Djano ORM?

Something like Products.object.filter(pk__in=pks).order_by(lambda p: ...pk.find[p.id])

https://gist.github.com/cpjolicoeur/3590737?permalink_comment_id=2202866#gistcomment-2202866

This seems to be exactly what I'm looking for.

SELECT * FROM foo WHERE id IN (3, 1, 2) ORDER BY array_position(ARRAY[3, 1, 2], id);

Is that possible to use extra() with array_position somehow perhaps?

Answer 1

With ORM, you can annotate with array_position function, and then order by annotated field:

Products.objects.filter(
    pk__in=pks
).annotate(
    _array_order=Func(
        Value(pks),
        F("pk"),
        function="array_position",
    )
).order_by(
    "_array_order"
)

However, in many cases converting from QuerySet to list and ordering with python code should be also okay.