How to use "AND" in a Django filter?

Leveraging Q objects for complex queries

For incorporating sophisticated query conditions, Django offers the Q object. It enables combining individual conditions with & (AND), | (OR), and ~ (NOT) operators.

Here's how you create an AND condition using Q objects with multiple fields:

from django.db.models import Q

# Hunting for those rare gems by John Smith from 2021. And oh, they must be Fiction!
books = Book.objects.filter(Q(author='John Smith') & Q(year=2021) & Q(genre='Fiction'))


This match books authored by John Smith, published in the year 2021, and belonging to the Fiction genre.

Efficiency with chained filter() methods

In basic scenarios, you can achieve more efficiency and enhance readability by chaining filters:

# Let's chain it up, one filter at a time!
books = Book.objects.filter(author='John Smith').filter(year=2021)


Each subsequent filter(), narrows down the query results further, effectively integrating implicit AND conditions between each filter.

LIKE conditions and text search

For searching text fields through a LIKE SQL operation, Django provides the __icontains lookup:

# Find me some 'adventurous quests', will ya?
books = Book.objects.filter(title__icontains='adventure', summary__icontains='quest')


This retrieves all books having a title encompassing adventure AND a summary incorporating quest, similar to SQL's LIKE operator with % wildcards.

Watch out for some gotchas!

When dealing with reverse relationships or ManyToMany fields, constructing filters can sometimes yield unanticipated results.

Subquery performance

Misuse of chaining or Q objects, especially with subqueries, can result in performance issues. Execute analysis for query execution plans and utilize select_related or prefetch_related for query optimization.

The unexpected exclusions

The use of .exclude() in combination with Q objects can produce unforeseen patterns due to logical negation. Be frugal with your uses of combined .exclude() or ~Q().

# This is Python. The unexpected is a part of the journey!
books = Book.objects.exclude(~Q(author='John Smith') | ~Q(year=2021))


The order of conditions

Remember, the operator precedence matters in Python as much as in life. For complex Q object expressions, use parentheses to control the order of evaluations.