Django QuerySet Filtering Notes

These are notes that condense the Django 1.5 filter annotation documentation (mostly Field lookups), along with various points of detail I’ve encountered.

General Matching

Annotation Notes
x__exact=value Exact match
x__exact=None IS NULL
x__iexact=value Case insensitive string match
x__isnull=True x IS NULL
x__isnull=False x IS NOT NULL
x__contains=value Case sensitive substring match.
x__icontains=value Case insensitive substring match.
x__in=[value1, value2, …] Match any in a list
x__gt=valuex__gte=valuex__lt=valuex__lte=value greater thangreater than or equallesser thanlesser than or equal
x__startswith=value LIKE ‘value%’
x__istartswith=value Case insensitive LIKE ‘value%’
x__endswith=value LIKE ‘%value’
x__iendswith=value >Case insensitive LIKE ‘%value’

Inverting Logic (NOT)

The logical inverse is provided by using the .exclude() method instead of .filter().

Chaining Filters

Complex queries are formed by chaining .filter(), .exclude(), and other operations.

Ranges (BETWEEN)

Ranges use the “__range” annotation set to a 2-tuple that contains the beginning and ending values. For example:

results = A.objects.filter(measurement__range=(10, 20))

will translate into

SELECT ... WHERE measurement BETWEEN 10 AND 20;


Dates always have the potential to be a little tricky.

First of all, the Django 1.5 docs warn that dates and DateTimeField should not be mixed because of the implicit “00:00:00” for the end of range date, meaning the end of range date effectively is skipped except for midnight exactly.

Date Ranges

Date ranges use the __range annotation with a object.

start_date =, 1, 1)
end_date =, 3, 31)
results = Entry.objects.filter(pub_date__range=(start_date, end_date))

Date Precision

Djano allows querying by year, month, or day precision. This is done by appending an extra annotation to the field name.

  • __year
  • __month
  • __day
  • __week_day – 1=Sunday 7=Saturday

For example,

results = Entry.objects.filter(pub_date__year=2005)

will match all records with pub_date between 1 January 2005 and 31 December 2005.


Subqueries are formed with the “__in” annotation. Instead of providing a list of values, provide another inner QuerySet.

The QuerySet should be model objects, or a single field value. A single field value can be obtained by using the .values() function.


This performs a subquery on model objects.

inner = A.objects.filter(foo__exact=’bar’)
result = B.objects.filter(a__in=inner)


This performs a subquery against a field value.

inner = A.objects.filter(foo__exact=’bar’).values(‘name’)
result = B.objects.filter(a__name__in=inner)

Master/Detail Joins

These I’m still a little fuzzy on. The Django docs have some information on how they are handled in Following relationships “backward”.

One query I wished to perform was to get a list of suppliers with orders, ignoring those suppliers from whom no orders were placed. The two models are related this way:

class Supplier(models.Model):

class Order(models.Model):
  supplier = models.ForeignKey(Supplier)

Note that Django creates an implicit “order” attribute on the Supplier class. This can be used for filtering with the caveat that this performs an outer join, so one must run the result through distinct().

results = Supplier.objects.filter(order__isnull=False).distinct()


Thanks to FunkyBob on the #django IRC channel on freenode for his insights.

This entry was posted in Programming and tagged , , , , . Bookmark the permalink.

Leave a Reply