{
  "$type": "site.standard.document",
  "canonicalUrl": "https://rednafi.com/python/sort-by-a-custom-sequence-in-django/",
  "description": "Sort Django querysets by custom attribute sequences using Case and When expressions for database-level ordering with SQL CASE statements.",
  "path": "/python/sort-by-a-custom-sequence-in-django/",
  "publishedAt": "2023-05-09T00:00:00.000Z",
  "site": "at://did:plc:fgtm2c26vfcj74rfmeggbyqj/site.standard.publication/3mnl6f7ob462z",
  "tags": [
    "Python",
    "Django",
    "Database"
  ],
  "textContent": "I needed a way to sort a Django queryset based on a custom sequence of an attribute.\nTypically, Django allows sorting a queryset by any attribute on the model or related to it\nin either ascending or descending order. However, what if you need to sort the queryset\nfollowing a custom sequence of attribute values?\n\nSuppose, you're working with a model called Product where you want to sort the rows of the\ntable based on a list of product ids that are already sorted in a particular order. Here's\nhow it might look:\n\nTurns out, this is a great case where Django's Case and When can come in handy. With\nthese, Django exposes the underlying SQL's way of performing conditional logic through\nCASE and WHEN statements. They allow you to return different values or expressions based\non some criteria. Think of them as similar to IF-THEN-ELSE statements in other programming\nlanguages. Primarily, there are two types of CASE expressions: simple and searched.\n\nSimple CASE expression\n\nA simple CASE expression compares an input expression to a list of values and returns the\ncorresponding result. Here's the syntax:\n\nThe input_expression can be any valid SQL expression. The data types of the\ninput_expression and each value must be the same or must be an implicit conversion.\n\nThe WHEN clauses are evaluated in order, from top to bottom. The first one that matches\nthe input_expression determines the result of the CASE expression. If none of the values\nmatch, the ELSE clause is executed. If the ELSE clause is omitted and no values match,\nthe CASE expression returns NULL. For example, suppose we have a table called products\nwith the following data:\n\nWe can use a simple CASE expression to assign a label to each product based on its\ncategory:\n\nThe output would be:\n\nSearched CASE expression\n\nA searched CASE expression evaluates a list of Boolean expressions and returns the\ncorresponding result. The syntax looks as follows:\n\nThe conditions can be any valid Boolean expressions. Just like simple CASE expressions,\nhere also, the data types of each result must be the same or must be an implicit conversion.\n\nAs before, the WHEN clauses are evaluated in order, from top to bottom. The first one that\nevaluates to TRUE determines the result of the CASE expression. If none of the\nconditions are TRUE, the ELSE clause is executed. If the ELSE clause is omitted and no\nconditions are TRUE, the CASE expression returns NULL.\n\nFor example, we can use a searched CASE expression to calculate a discount for each\nproduct based on its price:\n\nThe output would be:\n\nUsing searched CASE expressions to order querysets\n\nWith the intro explanations out of the way, here's how you can sort the products table\nintroduced in the previous section by a list of product ids:\n\nPrinting the queryset will return the following output:\n\nHere, we're trying to sort the products queryset by the product ids in the same order\nspecified in the product_ids list. First, we build a Case expression where we're\niterating through the product ids and defining the designated positions of the ids based on\ntheir positions in the list. Then we filter the products queryset by the ids and pass the\npreferred expression to the .order_by method. To see the underlying SQL generated by\nDjango, you can print the result of products_sorted.query:\n\nYou can directly run this query against the database and get the same result. Notice how\nDjango is taking advantage of a searched CASE expression to sort the queryset in the\ndesired way. This also allows sorting by a custom sequence of an attribute related to the\ntarget model. So you can do this:\n\nHere's what the Product and Order models look like:\n\nOrder has a foreign key relationship with Product and we're sorting the product queryset\nbased on a custom sequence of order ids. The query generates the SQL below:\n\nRunning the query gives us the following output:\n\nFurther reading\n\n- [Django get a queryset from an array of id's in a specific order - Stack Overflow]\n\n\n\n\n[django get a queryset from an array of id's in a specific order - stack overflow]:\n    https://stackoverflow.com/questions/4916851/django-get-a-queryset-from-array-of-ids-in-specific-order",
  "title": "Sorting a Django queryset by a custom sequence of an attribute"
}