Sorting a Django queryset by a custom sequence of an attribute
I needed a way to sort a Django queryset based on a custom sequence of an attribute. Typically, Django allows sorting a queryset by any attribute on the model or related to it in either ascending or descending order. However, what if you need to sort the queryset following a custom sequence of attribute values?
Suppose, you're working with a model called Product where you want to sort the rows of the table based on a list of product ids that are already sorted in a particular order. Here's how it might look:
Turns out, this is a great case where Django's Case and When can come in handy. With these, Django exposes the underlying SQL's way of performing conditional logic through CASE and WHEN statements. They allow you to return different values or expressions based on some criteria. Think of them as similar to IF-THEN-ELSE statements in other programming languages. Primarily, there are two types of CASE expressions: simple and searched.
Simple CASE expression
A simple CASE expression compares an input expression to a list of values and returns the corresponding result. Here's the syntax:
The input_expression can be any valid SQL expression. The data types of the input_expression and each value must be the same or must be an implicit conversion.
The WHEN clauses are evaluated in order, from top to bottom. The first one that matches the input_expression determines the result of the CASE expression. If none of the values match, the ELSE clause is executed. If the ELSE clause is omitted and no values match, the CASE expression returns NULL. For example, suppose we have a table called products with the following data:
We can use a simple CASE expression to assign a label to each product based on its category:
The output would be:
Searched CASE expression
A searched CASE expression evaluates a list of Boolean expressions and returns the corresponding result. The syntax looks as follows:
The conditions can be any valid Boolean expressions. Just like simple CASE expressions, here also, the data types of each result must be the same or must be an implicit conversion.
As before, the WHEN clauses are evaluated in order, from top to bottom. The first one that evaluates to TRUE determines the result of the CASE expression. If none of the conditions are TRUE, the ELSE clause is executed. If the ELSE clause is omitted and no conditions are TRUE, the CASE expression returns NULL.
For example, we can use a searched CASE expression to calculate a discount for each product based on its price:
The output would be:
Using searched CASE expressions to order querysets
With the intro explanations out of the way, here's how you can sort the products table introduced in the previous section by a list of product ids:
Printing the queryset will return the following output:
Here, we're trying to sort the products queryset by the product ids in the same order specified in the product_ids list. First, we build a Case expression where we're iterating through the product ids and defining the designated positions of the ids based on their positions in the list. Then we filter the products queryset by the ids and pass the preferred expression to the .order_by method. To see the underlying SQL generated by Django, you can print the result of products_sorted.query:
You can directly run this query against the database and get the same result. Notice how Django is taking advantage of a searched CASE expression to sort the queryset in the desired way. This also allows sorting by a custom sequence of an attribute related to the target model. So you can do this:
Here's what the Product and Order models look like:
Order has a foreign key relationship with Product and we're sorting the product queryset based on a custom sequence of order ids. The query generates the SQL below:
Running the query gives us the following output:
Further reading
Discussion in the ATmosphere