How to use Django order_by?

Sorting data stored in the database can often be crucial for your application. Luckily, Django has integrated methods over querysets that help us do that without writing SQL queries.

Django order_by is a method that sorts existing queryset by fields passed as arguments. Queryset can be sorted in ascending, descending, or random order (depending on arguments).

Why can’t we sort queryset using Python to manipulate data?
Sorting using Python would be much slower and expensive – converting the queryset to list will evaluate it, bringing performance issues.

All of the outputs and queries are made on the following models:

from django.db import models

class Author(models.Model):
   first_name = models.CharField(max_length=512)
   last_name = models.CharField(max_length=512)

class Book(models.Model):
   title = models.CharField(max_length=512)
   author = models.ForeignKey(Author, on_delete=models.CASCADE)

The database is prepopulated with data:

Authors table.

id	first_name	last_name
1	William	Shakespeare
2	Miguel	de Cervantes

Books table.

id	title	author_id
1	Hamlet	1
2	Romeo and Juliet	1
3	Macbeth	1
4	Don Quixote	2

Django order_by ascending order

Most common and default way of sorting while using Django’s order_by method. Based on the type of data we are sorting, we get an ascending order – for numbers; we sort lowest numbers first, strings are sorted alphabetically (from A to Z), date-times from lowest dates to highest (lowest being first).

Parameters we can send to Django order_by in the Authors table are id, first_name, last_name, or all combined. For example, for the Book model, we can send id, title, author_id, author__first_name, and author__last_name.

Let’s try it out.

>>> Author.objects.all().order_by('first_name')
<QuerySet [{'first_name': 'Miguel'}, {'first_name': 'William'}]>

As expected, the queryset has changed. Every query in Django ORM is translated to SQL query. For example, this query is translated to:

SELECT * FROM "book" ORDER BY "book"."title" ASC

Additionally, Django order_by enables us to order by parameters that are connected via foreign key.

For example, if we want to order Books by their authors’ first names.

>>>Book.objects.all().order_by('author__first_name')
<QuerySet [{'title': 'Don Quixote'}, {'title': 'Hamlet'}, {'title': 'Romeo and Juliet'}, {'title': 'Macbeth'}]>

Accordingly, translating ORM query to SQL query, we get:

SELECT * FROM "book" INNER JOIN "author" ON ("book"."author_id" = "author"."id") ORDER BY "author"."first_name" ASC

Two tables are connected using INNER JOIN and matching ids from the authors’ foreign keys. Then, after the new table is created, we sort it by the author’s first name.

Django order_by descending order

Contrary to ascending order, another Django order_by option is descending order. Descending order  – for numbers, we sort the highest numbers first, strings are sorted alphabetically (from Z to A), date-times from highest dates to lowest (highest being first).

Descending order can be achieved by adding the prefix ‘-‘ to the argument in the order_by method.

>>>Author.objects.all().order_by('-first_name')
<QuerySet [{'first_name': 'William'}, {'first_name': 'Miguel'}]>

Which is translated into:

SELECT * FROM "author" ORDER BY "author"."first_name" DESC

INFO - 
You can achieve the same result by using the default Django order_by method (without prefix) but adding the .reverse() method.

>>>Author.objects.all().order_by('first_name').reverse()
<QuerySet [{'first_name': 'William'}, {'first_name': 'Miguel'}]>

The SQL query is the same as the above.

SELECT * FROM "author" ORDER BY "author"."first_name" DESC

Django order_by random order

Django order_by offers the possibility of random order by using the operator ‘?’. However, as the name suggests, if using this method, order_by mixes up objects in queryset in no particular order.

>>>Book.objects.all().order_by('?')
<QuerySet [{'title': 'Don Quixote'}, {'title': 'Macbeth'}, {'title': 'Romeo and Juliet'}, {'title': 'Hamlet'}]>

As you can imagine, an SQL query is created supporting random queryset generation.

SELECT * FROM "book" ORDER BY RAND() ASC

WARNING - Do not use in production! Random order queries are slow and expensive.

Django order_by multiple fields

Previous examples covered how to order and change its order, but often we want to sort our queryset by multiple fields based on the priority of the fields – field passed first has bigger priority than field passed second, etc.

Let’s see an example.

>>>Book.objects.all().order_by('author__first_name', 'title')
<QuerySet [{'title': 'Don Quixote'}, {'title': 'Hamlet'}, {'title': 'Macbeth'}, {'title': 'Romeo and Juliet'}]>

Book objects are first sorted by author first_name (Miguel being first and William second) – that’s why Don Quixote is first in resolving queryset. Books written by William are then sorted by title in ascending order.

Previous query is translated to:

SELECT * INNER JOIN "author" ON ("book"."author_id" = "author"."id") ORDER BY "author"."first_name" ASC, "book"."title" ASC

Summary

Django order_by method is a popular method for sorting querysets using queries on the database. Order_by can sort queryset in ascending, descending, and random order with one or multiple fields.