Posted by Marta on December 18, 2020 Viewed 4609 times
How to use the database method order by and filter in django. Help you to select the specific entries from the database and receive them in a given order.
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
Let’s see how to use these methods with Django, the python library.
.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() 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')
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
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'
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')
__contains, you can indicate the filter should perform a contains operation.
There are a few more operations available like
gte(greater than), and
range( equivalent to the between SQL operator).
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')
You could also combine both operations
.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.
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')
To summarise, in this article, we covered how to filter and sort database entries using Django. You can use
.exclude() to filter, or
.get() to retrieve just a single object. To sort the results, you can use the
I hope you enjoy the article and find it useful. Thank you so much for reading and supporting this blog. Happy Coding!
Steady pace book with lots of worked examples. Starting with the basics, and moving to projects, data visualisation, and web applications
Unique lay-out and teaching programming style helping new concepts stick in your memory
Great guide for those who want to improve their skills when writing python code. Easy to understand. Many practical examples
Perfect Boook for anyone who has an alright knowledge of Java and wants to take it to the next level.
Excellent read for anyone who already know how to program and want to learn Best Practices
Perfect book for anyone transitioning into the mid/mid-senior developer level
Great book and probably the best way to practice for interview. Some really good information on how to perform an interview. Code Example in Java