These are notes that condense the Django 1.5 filter annotation documentation (mostly Field lookups), along with various points of detail I’ve encountered.
||Case insensitive string match
||x IS NULL
||x IS NOT NULL
||Case sensitive substring match.
||Case insensitive substring match.
|x__in=[value1, value2, …]
||Match any in a list
||greater thangreater than or equallesser thanlesser than or equal
||Case insensitive LIKE ‘value%’
||>Case insensitive LIKE ‘%value’
Inverting Logic (NOT)
The logical inverse is provided by using the .exclude() method instead of .filter().
Complex queries are formed by chaining .filter(), .exclude(), and other operations.
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 use the __range annotation with a datetime.date() object.
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))
Djano allows querying by year, month, or day precision. This is done by appending an extra annotation to the field name.
- __week_day – 1=Sunday 7=Saturday
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)
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:
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.