Django QuerySet Custom Ordering by ID

Question

Given a list of ids/pks, I'd like to generate a QuerySet of objects ordered by the index in the list.

Normally I'd begin with:

pk_list = [5, 9, 2, 14]
queryset = MyModel.objects.filter(pk__in=pk_list)

This of course returns the objects, but in the order of the models meta ordering property, and I wish to get the records in the order of the pks in pk_list.

The end result has to be one QuerySet object (not a list), as I wish to pass the ordered QuerySet to Django's ModelMultipleChoiceField form field.

Answer 1

There isn't a built-in way to do this.

If you're using MySQL, you can use that database's FIELD() function to set up a custom ordering sequence on your model, and sort by that. This would work:

pk_list = [5, 9, 2, 14]
ordering = 'FIELD(`id`, %s)' % ','.join(str(id) for id in pk_list)
queryset = MyModel.objects.filter(pk__in=[pk_list]).extra(
               select={'ordering': ordering}, order_by=('ordering',))

Answer 2

In Django 2.2, I was able to order QuerySet based on an order list using following approach:

pk_list = [4, 23, 10, 9]
preserved = Case(*[When(pk=pk, then=pos) for pos, pk in enumerate(pk_list)])
new_qs = qs.filter(pk__in=pk_list).order_by(preserved)

Answer 3

I do not know of a way to do this using a filter condition. If your database will return the rows in the order of the IN clause then you may be able to combine Django's extra method with the ROWNUM provided by your database to achieve this.

For e.g.:

queryset = MyModel.objects.filter(pk__in=[pk_list]).extra(
       select: {'rownum': 'row_num()'}).order_by('rownum')

Where row_num() is assumed to be a database function that returns the row number of the current row. Postgresql 8.4+ supports row_num() but I do not know how to order the rows returned using the same order as the values in the IN clause.

I think a better way would be to sub class ModelMultipleChoiceField and add custom sorting logic when rendering.