Home Developers & Open Source How to Filter for Values in a Django QuerySet?

How to Filter for Values in a Django QuerySet?

by Rithik Manchanda
0 comment 6 minutes read

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: titleauthor, 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.

You may also like

Leave a Comment

About Us

Engaging content across diverse genres, from the latest in fashion and lifestyle to technology and travel, we offer something for every curious mind!

-
00:00
00:00
Update Required Flash plugin
-
00:00
00:00

Adblock Detected

Please support us by disabling your AdBlocker extension from your browsers for our website.