Q() Objects in Django

A nifty feature of Django that I use frequently is the Q object. It’s how you express what filters should be applied to your SQL query. It’s what goes into the WHERE clause of the generated SQL query. Django’s definition is a little more formal:

A Q() object represents an SQL condition that can be used in database-related operations

A good way to think about Q() objects is that anything that you would pass into a .filter() call can be used in an Q() object. What’s neat is that doing a .filter(~Q(...)) is the equivalent of .exclude(...). This comes in handy when dynamically building up query filters.

I think examples are helpful, so here are a few:

from datetime import timedelta

from django.db import models
from django.db.models import F, Q
from django.utils import timezone

class Species(models.Model):
    name = models.CharField(max_length=100)

class Pet(models.Model):
    species = models.ForeignKey(
	    Species, related_name="pets", on_delete=models.CASCADE
	)
    name = models.CharField(max_length=100)
    treats_needed = models.IntegerField()
    treats_given = models.IntegerField()
    last_given_treats = models.DateTimeField(null=True)

def get_starving_pets():
    """Find the pets who are _STARVING_"""
    # Was the pet given a treat yesterday or not at all
	given_treats_yesterday_or_earlier: Q = (
		Q(last_given_treats__isnull=True)
		| Q(last_given_treats__lte=timezone.now() - timedelta(days=1))
	)
	# Does the pet need a treat
	needs_treats: Q = Q(treats_needed__gt=F("treats_given"))

    return Pet.objects.filter(
        given_treats_yesterday_or_earlier,
        needs_treats,
    )

Now there are several different ways we can write this that should all be the same.

def get_starving_pets():
    """Find the pets who are _STARVING_"""
    # Don't define variables, instead use them as filter arguments directly.
    return Pet.objects.filter(
	    # All arguments to filter are ANDed together by default
        Q(treats_needed__gt=F("treats_given")),
        Q(last_given_treats__isnull=True)
		| Q(last_given_treats__lte=timezone.now() - timedelta(days=1)),
    )

The above approach doesn’t define variables for the Q() objects and uses them directly in the .filter() call. This is a bit inefficient though since it’s redundant to wrap treats_needed__gt=F("treats_given") in a Q() object.

def get_starving_pets():
    """Find the pets who are _STARVING_"""
    # Don't define variables and use them as filter arguments directly.
    return Pet.objects.filter(
        Q(last_given_treats__isnull=True)
		| Q(last_given_treats__lte=timezone.now() - timedelta(days=1)),
	    # Since this is using the keyword argument part of the .filter() API,
	    # we need to specify it after the Q() object which is used as a
	    # positional argument.
        treats_needed__gt=F("treats_given"),
    )

The above is likely the most concise way to express the filter. However, a person could also do the following:

def get_starving_pets():
    """Find the pets who are _STARVING_"""
    # Use logical operations on QuerySet instead of Q
    pets = Pet.objects.all()
    return (
	    pets.filter(treats_needed__gt=F("treats_given")) &
	    (
		    pets.filter(last_given_treats__isnull=True)
		    | pets.filter(last_given_treats__lte=timezone.now() - timedelta(days=1))
	    )
	)

Personally, I find the Q() object cleaner. I prefer to define all the filtering in a single .filter() call. This is a habit I formed to avoid multiple joins when filtering across model relationships; I’ll share more on that later.

Building Q objects dynamically

Another great use for Q() objects is building a filter for a QuerySet dynamically. A common case for this is creating basic search queries¹ by breaking up the search term into words and performing a contains check on all of them.

For example, if a person searched for a pet name with the term "Cat the Great", we’d want to end up with the following QuerySet:

Pet.objects.filter(
    Q(name__icontains="cat")
    | Q(name__icontains="the")
    | Q(name__icontains="great")
)

As you may have noticed, the above won’t work in a view because the QuerySet is based on the search term supplied by the user. To work around this, we can dynamically build a Q() object that does the filter we want.

name_search = Q()
for term in search_term.split(' '):
    name_search |= Q(name__icontains=term)

Pet.objects.filter(name_search)

Generally, this search approach is problematic as SQL queries with OR statements perform poorly. Though it’s perfectly fine for smaller projects or small datasets.

Composing Q objects

A neat byproduct of Q() objects is that you can now compose them. While QuerySet is intended to be composable, it’s not entirely. We’ll see how multiple .filter() calls across relationships can work differently than we expect later on. We also can’t easily store a QuerySet for future usage. But an Q() object can get closer. We can structure our application so that we can compose an Q() object dynamically.

The first thing to be aware of is that you can unpack a dictionary directly into a Q() object. For example, these three QuerySets are the same:

Pet.objects.filter(name__icontains="Meowy")
Pet.objects.filter(**{"name__icontains": "Meowy"})
Pet.objects.filter(Q(**{"name__icontains": "Meowy"}))

This means we can build a dictionary dynamically, then unpack that into a Q() object for a complex filter that’s implemented cleanly.

def find_pets_with_term(term: str, prefix_to_pet: str="") -> Q:
	if prefix_to_pet and not prefix_to_pet.endswith("__"):
		## If we're using the prefix, make sure it ends with `__`
		prefix_to_pet += "__"
	return Q(**{
	    f"{prefix_to_pet}name__icontains": term
	})

meowy_pets = Pet.objects.filter(
	find_pets_with_term(term="Meowy")
)

species_with_meowy_pets = Species.objects.filter(
	find_pets_with_term(term="Meowy", prefix_to_pet="pets"),
).distinct()

Now the above doesn’t look too complex. Well maybe the species_with_meowy_pets does, but that’s more because I added it in without you expecting it. That QuerySet will contain any species that has at least one pet with "meowy" in the name field.

If you wanted to avoid the caller needing to know what the path is to Pet, we can define these as class methods on the individual models:

class Pet(models.Model):
    ...
    @classmethod
    def q_pets_with_term(cls, term: str):
        return find_pets_with_term(term=term)


class Species(models.Model):
    ...
    @classmethod
    def q_pets_with_term(cls, term: str):
        return find_pets_with_term(term=term, prefix_to_pet="pets")

meowy_pets = Pet.objects.filter(
	Pet.q_pets_with_term(term)
)

species_with_meowy_pets = Species.objects.filter(
	Species.q_pets_with_term(term)
).distinct()

The reason I’m showing you this is that there will come a time when you want to dynamically compose a QuerySet based on some type of dynamic data. Maybe it’s input from the user, maybe it’s a stored model. Regardless, by composing your Q() objects in functions like the above, you can write logic that looks like this:

from django.utils import timezone

def filter_model(model_class, filtering_options):
	q = Q()
	if filtering_options.has_meowy:
		q &= model_class.q_pets_with_term(term="meowy")
	# I made this up, but but each model would define it's own
	# way to determine when it's been created
	if filtering_options.created_before_today:
		q &= model_class.q_created_before(threshold=timezone.now())
	# I made this up, but let's assume it's sanitized and safe
	# to use without a user accessing privileged information.
	if filtering_options.custom_json_filter:
		# This should only be done if you absolutely trust the data
		# going into `custom_json_filter` because it means you're
		# generating SQL queries entirely based on input that you
		# don't control.
		q &= Q(**json.loads(filtering_options.custom_json_filter))
	return model_class.objects.filter(q)

Now we call filter_model with any model that implements both q_pets_with_term and q_created_before and pass in a custom filtering_options object. That latter piece is definitely more complex and has a bit of handwaving. But if you’ve ever wanted to create a way for a user or system to access data and allow the caller to define what filtering options are applied, this may be of interest to you. It’s effectively the same principle as GraphQL, but in your own format. The details aren’t super important, though.

What I hope you take away is the understanding that you can build out custom filtering and utilize it across whatever model you want. And I’m hoping you picked up that you could store those custom filtering options too.

At the core of those ideas is the concept of composing the Q() object, though. You can dynamically AND and/or OR Q() objects to do whatever is needed in any situation. Understanding this can help you build out some truly fantastic features for your application.

Avoiding multiple joins when filtering across model relationships

Alright, at this point I’ve referenced this problem of Django making multiple joins when filtering across a model relationship a few times but haven’t explained it. In short, when you cross a many-to-many relationship or a one-to-many relationship (reverse foreign key) in a filter, Django automatically JOIN’s the model’s table to the query for that filter.

Species.objects.filter(pets__name__icontains="meowy")

The above QuerySet will generate a SQL statement that will automatically join the pet table to the query.

SELECT "app_species"."id",
       "app_species"."name"
FROM "app_species"
INNER JOIN "app_pet" ON ("app_species"."id" = "app_pet"."species_id")
WHERE "app_pet"."name" LIKE '%meowy%'

Where things get confusing is when we want to filter on another Pet property. Let’s say we would like to find those that haven’t been given a treat.

# The Pet filters will occur on the same join
meowy_and_hungry_species = Species.objects.filter(
	pets__name__icontains="meowy",
	pets__treats_given=0,
)

The above generates this SQL, where we can see there’s only one JOIN and both of the WHERE clauses operate on that joined table. This will find the Species that have a Pet that has both a meowy name and has been given zero treats.

SELECT "app_species"."id",
       "app_species"."name"
FROM "app_species"
INNER JOIN "app_pet" ON ("app_species"."id" = "app_pet"."species_id")
WHERE ("app_pet"."name" LIKE '%meowy%'
       AND "app_pet"."treats_given" = 0)

Now, if we chain two separate .filter() calls, we get a different SQL query and different results.

# There will be two joins to Pet, one for each filter
meowy_and_hungry_species = Species.objects.filter(
	pets__name__icontains="meowy",
).filter(
	pets__treats_given=0,
)

This generates the SQL where there are two joins. This is actually going to find any species that has any Pet that has a meowy name and any Pet that has been given zero treats. These do not need to be the same Pet instance.

SELECT "app_species"."id",
       "app_species"."name"
FROM "app_species"
INNER JOIN "app_pet" ON ("app_species"."id" = "app_pet"."species_id")
INNER JOIN "app_pet" T3 ON ("app_species"."id" = T3."species_id")
WHERE ("app_pet"."name" LIKE '%meowy%'
       AND T3."treats_given" = 0)

At times, this can be helpful, but in my experience, this is mainly a footgun. I almost always want to continue to filter on the same joined relationship.

This is why I prefer to build my queries up with a Q() object and then pass that into a single .filter() call. It eliminates this footgun.

Takeaways

Let’s wrap this up!

Remember, Q() objects are how you express what filters should be applied to your SQL query. They are optional, and anything you can do with a .filter() or .exclude() call can be represented as a Q() object. You should reach for them when you’re building a QuerySet filter dynamically. Q() objects can form the basis of building composable filters, which is helpful when you want to combine business logic and dynamic filtering.

You won’t always need a Q() object, but when you do, it is your best ORM friend.

If you have thoughts, comments, or questions, please let me know. You can set up a meeting with me, or find me on the Fediverse, Django Discord server or use email.