How to Filter for Values in a Django QuerySet?

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: title, author, and date_published. We can quickly see that our database contains 20 books in total by using the count() method:

>>> 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

With 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 User.objects.filter(first_name__startswith='R').

What if you want to compare the first_name and last name? You can use the F object. Create some users first.

In [27]: User.objects.create_user(email="[email protected]", username="rohan", first_name="Rohan", last_name="Sachdeva")
Out[27]: <User: rohan>

In [28]: User.objects.create_user(email="[email protected]", username="prateek", first_name="Prateek", last_name="Prateek")
Out[28]: <User: prateek>

Now you can find the users where first_name==last_name

In [29]: User.objects.filter(last_name=F("first_name"))
Out[29]: <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 [41]: User.objects.create_user(email="[email protected]", username="druv", first_name="Druv", last_name="Dhingra")
Out[41]: <User: druv>
#...
In [46]: User.objects.annotate(first=Substr("first_name", 1, 1), last=Substr("last_name", 1, 1)).filter(first=F("last"))
Out[46]: <QuerySet [<User: prateek>, <User: druv>]>

F can also be used with __gt, __lt and other expressions.

Using 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 first_name.

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.