Creating a QuerySet from a list while preserving order using Django

In one of my recent Django projects, I ran into an issue when making a QuerySet based off a list of primary keys returned from a SOLR server. The order of those primary keys was important, but wasn't being preserved in the QuerySet.

The following snippet demonstrates the typical method used for creating a QuerySet from a list in Django.

pk_list = [5, 7, 1, 3, 4]
queryset = Article.objects.filter(pk__in=pk_list)

This works, but the order of queryset will not always match the order of elements in pk_list

I found the solution to this problem in a post by Mathieu Leplatre. Basically, you can specify the order of results when making a query using the following SQL syntax.

SELECT *
FROM articles
ORDER BY
  CASE
    WHEN id=5 THEN 0
    WHEN id=7 THEN 1
    WHEN id=1 THEN 2
    WHEN id=3 THEN 3
    WHEN id=4 THEN 4
  END;

This can also be written using Django's ORM system. The following snippet replaces the original snippet in this post and ensures that the ordering of elements in queryset matches the ordering of elements in pk_list

pk_list = [5, 7, 1, 3, 4]
clauses = ' '.join(['WHEN id=%s THEN %s' % (pk, i) for i, pk in enumerate(pk_list)])
ordering = 'CASE %s END' % clauses
queryset = Article.objects.filter(pk__in=pk_list).extra(
           select={'ordering': ordering}, order_by=('ordering',))