Django: order by and filter with examples

Posted by Marta on February 2, 2023 Viewed 24814 times

This article will share how to use the database method order by and filter in Django. These methods help you select the specific entries you want to retrieve from the database and receive them in a given order.

Any web application will need to list entities that meet specific criteria and render them sorted logically. For example, in an online store, you will frequently need to display products that belong to a given category order by relevance or price. The Django framework can perform these operations using .filter() and .order_by().

Let’s see how to use these methods with Django, the python library.

Table of Contents

Django order by

The .order_by() method allows you to return a query result sorted by any column you like, in ascending or descending order. Let’s see a simple example. We have a table called project containing all projects you work on for the last year. The table will look as follows in python Django code:

from django.db import models
class Project(models.Model):
    title = models.CharField(max_length=300)
    description = models.TextField(blank=True)
    color = models.CharField(max_length=10)
    updated_at = models.DateTimeField(auto_now=True)

The statement above indicates Django’s creation of a table named Project containing four columns: title, description, color, and the updated_at. The updated_at column will store the time last project’s update. For instance, if you change the description for one of the projects, then the updated_at column will update with the current time.

Now, let’s say you will like to retrieve a list of your projects, having the most recently updated at the top. To achieve this, you will need to order the projects by updated_at in descending order. See below how to do so:

myprojects = Project.objects.order_by('-updated_at')

Note the minus sign is indicating descending order. You could retrieve your project in ascending order as well, just removing the minus sign. See the example below:

myprojects = Project.objects.order_by('updated_at')

Order by multiple columns

The .order_by() method also permits ordering by several columns. For example, you might want to order your projects by title and colour, both ascending. To do so, you only need t pass another column to the method.

myprojects = Project.objects.order_by('title','color')

Django filter by

The Django framework also offers filtering capabilities. Django can filter the table entries by any column you like. Let’s see how to use Django filtering with an example. I will continue using the table projects with four columns: title, description, color, and updated_at.

In this example, I will like to filter by the color:

myproject_list = Project.objects.filter(color='#000000')

This code will return a list containing all projects with back color.

Behind the scenes, Django will translate this statement into SQL, adding a where clause to retrieve the subset that meets the filtering condition. Here is the equivalent SQL:

select title,description,color, updated_at
from project
where color='#000000'

You can also filter by several columns simultaneously, only passing different conditions to the .filter() method.

myproject_list = Project.objects.filter(color='#000000', title='My project')

The code above will return all projects with color black and with title equal to 'My project'. Several conditions will be added to the SQL where clause while translating the above statement into SQL, all connected by AND:

select title,description,color, updated_at
from project
where color='#000000' and title='My project'

Filter contains

We have seen how to use the exact match filter; however, Django offers functionality to filter by using more complex conditions, like contains. Let’s see this in action in the example below. We might want to retrieve any project that includes the word HTML within the description. Here is how you can do that:

html_project_list = Project.objects.filter(description__icontains='html')

Using __contains, you can indicate the filter should perform a contains operation.

There are a few more operations available like startswith, endswith, lte(less than), gte(greater than), and range( equivalent to the between SQL operator).

Excluding

What about negating a condition? You might want to exclude some entries from the list. For instance, in an online store, you might need to list products of a given category, excluding products sold out.

The Django framework offers the .exclude() method, which allows you to filter further with a negated condition.

For instance, in our project example, I could return all projects with a color different from black. See below the code:

non_black_project_list = Project.objects.exclude(color='#000000')

Note that you can combine the exclude method with a filter. For instance, you could retrieve any project that contains html in the description and is not black. See the code below:

html_non_black_project_list = Project.objects.filter(description__icontains='html').exclude(color='#000000')

Combine order_by and filter

You could also combine both operations .filter() and .order_by() and obtain a list of sorted entries that meet specific criteria. Following our project example, I could retrieve the projects with something to do with HTML, ordered by last time updated. The project that was updated most recently should be at the top, in other words, in descending order. See below the code to achieve that:

html_project_list = Project.objects.filter(description__icontains='html').order_by('-updated_at')

This code combines the previous statement in one single statement, so you can filter and order in one step.

Get one single object

What if you want to retrieve a single object? Usually, you use a filter when you are retrieving a subset of objects, and the number of objects is unknown. There could be one entry, or five entries, or something else. The .filter() method returns a QuerySet object, which can be manipulated as a list. However, when you are trying to retrieve just one object is more convenient to use the .get() method. Same as a filter, you need to pass as a parameter the filtering condition.

For instance, in our project example we could need to retrieve a project with an specific title. See how to do this below:

searched_project = Project.objects.get(title='My project')

Conclusion

To summarise, in this article, we covered how to filter and sort database entries using Django. You can use .filter() or .exclude() to filter, or .get() to retrieve just a single object. To sort the results, you can use the .order_by() method.

I hope you enjoy the article and find it useful. Thank you so much for reading and supporting this blog. Happy Coding!