Home » Django Tutorial » Django order_by

Django order_by

Summary: in this tutorial, you’ll learn how to use Django order_by to sort rows by one or more columns in ascending or descending order.

Introduction to the Django order_by() method

When defining a model, you can specify the default order of the results returned by a QuerySet by using the ordering option in the model’s Meta class.

We’ll use the Employee model for the demonstration. The Employee model maps to the hr_employee table in the database:

For example, to sort the employee by first and last names alphabetically, you can specify the first_name and last_name fields the ordering option in the Meta class as follows:

class Employee(models.Model):
    # ...

    class Meta:
        ordering = ['first_name', 'last_name']
Code language: Python (python)

To sort the values in descending order, you add the - character in front of the field name. For example, the following uses the ordering option to sort the employees by first name in descending order and last name in ascending order:

class Employee(models.Model):
    # ...

    class Meta:
        ordering = ['-first_name', 'last_name']
Code language: Python (python)

Also, you can use a query expression to make null values appear first or last in the sorted result:

from django.db.models import F

class Employee(models.Model):
    # ...

    class Meta:
        ordering = [F('first_name').asc(nulls_last=True)]
Code language: Python (python)

To override the default sort order, you use the order_by() method of a QuerySet:

order_by(*fields)Code language: Python (python)

The following example uses the order_by() to sort employees by first name in ascending order:

>>> Employee.objects.all().order_by('first_name')             
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 ORDER BY "hr_employee"."first_name" ASC
 LIMIT 21
Execution time: 0.001034s [Database: default]
<QuerySet [<Employee: Aaron Pearson>, <Employee: Adam Crane>, <Employee: Adam Stewart>, <Employee: Adrienne Green>, <Employee: Alan Johnson>, <Employee: Alexa West>, <Employee: Alicia Wyatt>, <Employee: Amanda Benson>, <Employee: Amber Brown>, <Employee: Amy Lopez>, <Employee: Amy Lee>, <Employee: Andre Perez>, <Employee: 
Andrea Mcintosh>, <Employee: Andrew Guerra>, <Employee: Andrew Dixon>, <Employee: Ann Chang>, <Employee: Anne Odom>, <Employee: Anthony Welch>, <Employee: Anthony Fuentes>, <Employee: Ashley Brown>, '...(remaining elements truncated)...']>
Code language: Python (python)

Like the ordering option, you can use the order_by() method to sort the employee by the first name in descending order:

>>> Employee.objects.all().order_by('-first_name') 
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 ORDER BY "hr_employee"."first_name" DESC
 LIMIT 21
Execution time: 0.000998s [Database: default]
<QuerySet [<Employee: William Wise>, <Employee: Wendy Reilly>, <Employee: Victoria Forbes>, <Employee: Victoria Schneider>, <Employee: Vicki Baker>, <Employee: Veronica Blackburn>, <Employee: Vanessa Allen>, <Employee: Valerie Nguyen>, <Employee: Tyler Coleman>, <Employee: Tyler Briggs>, <Employee: Troy Ashley>, <Employee: Travis Goodwin>, <Employee: Tony Jordan>, <Employee: Todd Evans>, <Employee: Timothy Dillon>, <Employee: Timothy Mckay>, <Employee: Timothy Williams>, <Employee: Timothy Lewis>, <Employee: Tiffany Holt>, <Employee: Tiffany Jackson>, '...(remaining elements truncated)...']>
Code language: Python (python)

The order_by() also allows you to sort the results by multiple fields. For example, the following uses the order_by() to sort employees by first and last name:

>>> Employee.objects.all().order_by('first_name','last_name')
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 ORDER BY "hr_employee"."first_name" ASC,
          "hr_employee"."last_name" ASC
 LIMIT 21
Execution time: 0.000998s [Database: default]
<QuerySet [<Employee: Aaron Pearson>, <Employee: Adam Crane>, <Employee: Adam Stewart>, <Employee: Adrienne Green>, <Employee: Alan Johnson>, <Employee: Alexa West>, <Employee: Alicia Wyatt>, <Employee: Amanda Benson>, <Employee: Amber Brown>, <Employee: Amy Lee>, <Employee: Amy Lopez>, <Employee: Andre Perez>, <Employee: 
Andrea Mcintosh>, <Employee: Andrew Dixon>, <Employee: Andrew Guerra>, <Employee: Ann Chang>, <Employee: Anne Odom>, <Employee: Anthony Fuentes>, <Employee: Anthony Welch>, <Employee: Ashley Brown>, '...(remaining elements truncated)...']>
Code language: Python (python)

To order randomly, you can use a question mark ? like this:

>>> Employee.objects.all().order_by('?')                      
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 ORDER BY RANDOM() ASC
 LIMIT 21
Execution time: 0.010370s [Database: default]
<QuerySet [<Employee: Daniel Meyer>, <Employee: Todd Evans>, <Employee: Roger Robinson>, <Employee: Dwayne Williams>, <Employee: Michael Murphy>, <Employee: Daniel Friedman>, <Employee: Claudia Aguilar>, <Employee: Craig Hunter>, <Employee: Amanda Benson>, <Employee: Renee Wright>, <Employee: Wendy Reilly>, <Employee: Jamie Jackson>, <Employee: Philip Jones>, <Employee: Kelly Stewart>, <Employee: Barbara Vincent>, <Employee: Drew Gonzalez>, <Employee: Derek Owens>, <Employee: Lauren Mcdonald>, <Employee: Perry Rodriguez>, <Employee: Matthew Hernandez>, '...(remaining elements truncated)...']>
Code language: Python (python)

The order_by(‘?’) maybe slow and expensive, depending on the database that you’re using. The above-generated SQL is from PostgreSQL. Other databases may have different implementations.

Ordering by related models

The order_by() allows you to order by a field in a related model. The syntax of the field is the related model, followed by double underscores and the field name:

Entity.objects.order_by(related_model__field_name)Code language: Python (python)

We’ll use the Employee and Department models for the demonstration. The Employee and Department models map to the hr_employee and hr_department tables:


The following example uses the order_by() to sort employees by their department names:

>>> Employee.objects.all().order_by('department__name')
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 INNER JOIN "hr_department"
    ON ("hr_employee"."department_id" = "hr_department"."id")
 ORDER BY "hr_department"."name" ASC
 LIMIT 21
Execution time: 0.037173s [Database: default]
<QuerySet [<Employee: Brandy Morris>, <Employee: Jay Carlson>, <Employee: Jessica Lewis>, <Employee: Amanda Benson>, <Employee: Jacqueline Weaver>, <Employee: Patrick Griffith>, <Employee: Adam Stewart>, <Employee: Tiffany Holt>, <Employee: Amber Brown>, <Employee: Martin Raymond>, <Employee: Kyle Pratt>, <Employee: Cheryl Thomas>, <Employee: Linda Garcia>, <Employee: Jeanette Hendrix>, <Employee: Kimberly Gallagher>, <Employee: Kelly Stewart>, <Employee: Alan Johnson>, <Employee: 
William Wise>, <Employee: Debra Webb>, <Employee: Ryan Garcia>, '...(remaining elements truncated)...']>
Code language: Python (python)

The generated SQL shows that when ordering by a field in a related model, Django uses a join that joins the table of the current model (Employee) with the table of the related model (Department).

Summary

  • Use Django order_by() to sort data by one or more fields in ascending or descending order.
Did you find this tutorial helpful ?
Django loaddata
Previously
Django loaddata
Up Next
Django Limit Offset
Django Limit Offset