What are filters ?
Easily the most important method when working with Django models and the underlying QuerySets is the
filter() method, which allows you to generate a QuerySet of objects that match a particular set of filtered parameters.
For example, our application has a
Book model with a few basic fields:
date_published. We can quickly see that our database contains
20 books in total by using the
>>> Book.objects.count() 20
Working with filters
By by using
filter(), we can retrieve a QuerySet of just those books that were published within the last 90 days period, like so:
>>> from datetime import datetime, timedelta >>> Book.objects.filter(date_published__gte=datetime.now() - timedelta(days=90)).count() 3
filter(), we can determine that merely 3 of our 20 total books were published within the last 90 day period.
Now what is a QuerySet?
A QuerySet represents a collection of objects from your database. It can have zero, one or many filters. Filters narrow down the query results based on the given parameters. In SQL terms, a QuerySet equates to a SELECT statement, and a filter is a limiting clause such as WHERE or LIMIT.
Filtering a QuerySet with criteria based on comparing their field values
Django makes it easy to filter based on fixed values. To get all
User objects with
first_name starting with
'R', you can do
What if you want to compare the first_name and last name? You can use the
F object. Create some users first.
In : User.objects.create_user(email="[email protected]", username="rohan", first_name="Rohan", last_name="Sachdeva") Out: <User: rohan> In : User.objects.create_user(email="[email protected]", username="prateek", first_name="Prateek", last_name="Prateek") Out: <User: prateek>
Now you can find the users where
In : User.objects.filter(last_name=F("first_name")) Out: <QuerySet [<User: prateek>]>
F also works with calculated field using annotate. What if we wanted users whose first and last names have same letter?
You can set the first letter from a string using
Substr("first_name", 1, 1), so we do.
In : User.objects.create_user(email="[email protected]", username="druv", first_name="Druv", last_name="Dhingra") Out: <User: druv> #... In : User.objects.annotate(first=Substr("first_name", 1, 1), last=Substr("last_name", 1, 1)).filter(first=F("last")) Out: <QuerySet [<User: prateek>, <User: druv>]>
F can also be used with
__lt and other expressions.
Q objects for complex Queries
If you want to
OR your conditions.
>>> from django.db.models import Q >>> queryset = User.objects.filter( Q(first_name__startswith='R') | Q(last_name__startswith='D') ) >>> queryset <QuerySet [<User: Ricky>, <User: Ritesh>, <User: Radha>, <User: Raghu>, <User: rishab>]>
If you want to
AND your conditions.
>>> queryset = User.objects.filter( Q(first_name__startswith='R') & Q(last_name__startswith='D') ) >>> queryset <QuerySet [<User: Ricky>, <User: Ritesh>, <User: rishab>]>
Finding distinct field values from QuerySet
You want to find users whose names that have not been repeated. You can do this like this
distinct = User.objects.values( 'first_name' ).annotate( name_count=Count('first_name') ).filter(name_count=1) records = User.objects.filter(first_name__in=[item['first_name'] for item in distinct])
This is different from
User.objects.distinct("first_name").all(), which will pull up the first record when it encounters a distinct
So now we’ve seen that the QuerySets that you use in your apps can have significant real-world performance implications. However, with some care and understanding of the simple concepts behind Django’s QuerySets, you can improve your code and become a better Django developer.
But more than that, I hope that you take away from this article the realization that you shouldn’t be afraid to read Django’s source code to see how something works, or to build minimal working examples or simple tools to explore problems within the Django shell.