[Django]-Django get a QuerySet from array of id's in specific order

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.

293πŸ‘

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

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

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

13πŸ‘

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

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.

0πŸ‘

I solve this problem on PostgreSQL using array_position

from django.db.models import F, Func, IntegerField, OrderBy
from django.db.models.functions import Cast


class ArrayPosition(Func):
    function = "array_position"

    def __init__(self, items, field):
        super().__init__(items, field, output_field=IntegerField(null=True))


pk_list = [10, 2, 1]

queryset = MyModel.objects.annotate(
    pk_order=ArrayPosition(pk_list, Cast("pk", IntegerField()))
).order_by(OrderBy(F("pk_order"), nulls_last=True))

I generates smaller SQL unlike Case/When solution (when there are lot of items).

SELECT
    ...,
    array_position(ARRAY[10, 4, 1], ("my_model"."id")::integer) AS "pk_order"
FROM "my_model"
ORDER BY pk_order ASC NULLS LAST 

vs.

SELECT
    *
FROM "my_model"
ORDER BY CASE
    WHEN "my_model"."id" = 10 THEN 0
    WHEN "my_model"."id" = 4 THEN 1
    WHEN "my_model"."id" = 1 THEN 2
    ELSE NULL END ASC
πŸ‘€JiriK

Leave a comment