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:

[code lang=”python”]
results = A.objects.filter(measurement__range=(10, 20))
[/code]

will translate into

[code lang=”sql”]
SELECT … WHERE measurement BETWEEN 10 AND 20;
[/code]

Dates

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 datetime.date() object.

[code lang=”python”]
start_date = datetime.date(2005, 1, 1)
end_date = datetime.date(2005, 3, 31)
results = Entry.objects.filter(pub_date__range=(start_date, end_date))
[/code]

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,

[code lang=”python”]
results = Entry.objects.filter(pub_date__year=2005)
[/code]

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

Subqueries

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.

Example

This performs a subquery on model objects.

[code lang=”python”]
inner = A.objects.filter(foo__exact=’bar’)
result = B.objects.filter(a__in=inner)
[/code]

Example

This performs a subquery against a field value.

[code lang=”python”]
inner = A.objects.filter(foo__exact=’bar’).values(‘name’)
result = B.objects.filter(a__name__in=inner)
[/code]

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:

[code lang=”python”]
class Supplier(models.Model):

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

[/code]

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().

[code lang=”python”]
results = Supplier.objects.filter(order__isnull=False).distinct()
[/code]

Credits

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

Leave a Reply