/Django-QuerySet-Cheatsheet

A cheatsheet (or really more of a reference) for when I am solving the worlds problems in the Django shell

Cheatsheet for Django QuerySets

Current Django Version: 1.8

Methods that return new QuerySets

Can be chained:

Entry.objects.filter(**kwargs).exclude(**kwargs).order_by(**kwargs)
Entry.objects.filter(id__in=[1, 3, 4])
SELECT ... WHERE id IN (1, 3, 4);

inner_qs = Blog.objects.filter(name__contains='Cheddar')
entries = Entry.objects.filter(blog__in=inner_qs)
SELECT ... WHERE blog.id IN (SELECT id FROM ... WHERE NAME LIKE '%Cheddar%')
Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3), headline='Hello')
SELECT ... WHERE NOT (pub_date > '2005-1-3' AND headline = 'Hello')

Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3))/
.exclude(headline='Hello')
SELECT ... WHERE NOT pub_date > '2005-1-3' AND NOT headline = 'Hello'
>>> from django.db.models import Count
>>> q = Blog.objects.annotate(Count('entry'))
# The name of the first blog
>>> q[0].name
'Blogasaurus'
# The number of entries on the first blog
>>> q[0].entry__count
42

>>> q = Blog.objects.annotate(number_of_entries=Count('entry'))
# The number of entries on the first blog, using the name provided
>>> q[0].number_of_entries
42
Entry.objects.filter(pub_date__year=2005).order_by('-pub_date', 'headline')
The result above will be ordered by pub_date descending, 
then by headline ascending. 
The negative sign in front   of "-pub_date" indicates descending order. 
Ascending order is implied. 

# No Join
Entry.objects.order_by('blog_id')
# Join
Entry.objects.order_by('blog__id')
Use the reverse() method to reverse the order in 
which a queryset’s elements are returned. 
Calling reverse() a second time restores the ordering back to the normal direction.

my_queryset.reverse()[:5]
When using distinct() and values() together, be careful when ordering by fields
not in the values() call.

When you specify field names, you must provide an order_by() in the QuerySet, 
and the fields in order_by() must  start with the fields in distinct(), 
in the same order.

Author.objects.distinct()

Entry.objects.order_by('pub_date').distinct('pub_date')

Entry.objects.order_by('blog__name', 'mod_date').distinct('blog__name', 'mod_date')
Blog.objects.filter(name__startswith='Beatles').values()
[{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}]

Entry.objects.values('blog_id')
[{'blog_id': 1}, ...]

Blog.objects.values('name', 'entry__headline')
[{'name': 'My blog', 'entry__headline': 'An entry'},
    {'name': 'My blog', 'entry__headline': 'Another entry'}, ...]
Entry.objects.values_list('id', 'headline')
[(1, 'First entry'), ...]

Entry.objects.values_list('id', flat=True).order_by('id')
[1, 2, 3, ...]
field should be the name of a DateField of your model. 
kind should be either "year", "month" or "day". 
Each datetime.date object in the result list is “truncated” to the given type.

* "year" returns a list of all distinct year values for the field.
* "month" returns a list of all distinct year/month values for the field.
* "day" returns a list of all distinct year/month/day values for the field.

Entry.objects.dates('pub_date', 'year')
[datetime.date(2005, 1, 1)]

Entry.objects.dates('pub_date', 'month')
[datetime.date(2005, 2, 1), datetime.date(2005, 3, 1)]

Entry.objects.dates('pub_date', 'day')
[datetime.date(2005, 2, 20), datetime.date(2005, 3, 20)]

Entry.objects.dates('pub_date', 'day', order='DESC')
[datetime.date(2005, 3, 20), datetime.date(2005, 2, 20)]
tzinfo defines the time zone to which datetimes are converted prior to truncation. 
Indeed, a given datetime has different representations depending on the time zone in use. 
This parameter must be a datetime.tzinfo object. 
If it’s None, Django uses the current time zone. It has no effect when USE_TZ is False.

This function performs time zone conversions directly in the database. 
As a consequence, your database must be able to interpret the value of tzinfo.tzname(None). 
This translates into the following requirements:

* SQLite: install pytz — conversions are actually performed in Python.
* PostgreSQL: no requirements (see Time Zones).
* Oracle: no requirements (see Choosing a Time Zone File).
* MySQL: install pytz and load the time zone tables with mysql_tzinfo_to_sql.
Calling none() will create a queryset that never returns any objects and 
no query will be executed when accessing the results. 
A qs.none() queryset is an instance of EmptyQuerySet.

Entry.objects.none()
[]

from django.db.models.query import EmptyQuerySet
isinstance(Entry.objects.none(), EmptyQuerySet)
True
Returns a copy of the current QuerySet (or QuerySet subclass).
When a QuerySet is evaluated, it typically caches its results. 
If the data in the database might have changed since a QuerySet was evaluated, 
you can get updated results for the same query by calling all() 
on a previously evaluated QuerySet.
e = Entry.objects.get(id=5) # Hits the database.
b = e.blog # Hits the database again to get the related Blog object.

# Hits the database.
e = Entry.objects.select_related('blog').get(id=5)

# Doesn't hit the database, because e.blog has been prepopulated
# in the previous query.
b = e.blog

b = Book.objects.select_related('author__hometown').get(id=4)
p = b.author         # Doesn't hit the database.
c = p.hometown       # Doesn't hit the database.

b = Book.objects.get(id=4) # No select_related() in this example.
p = b.author         # Hits the database.
c = p.hometown       # Hits the database.

Methods that do not return QuerySets

Field lookups

Field lookups are how you specify the meat of an SQL WHERE clause. They’re specified as keyword arguments to the QuerySet methods filter(), exclude() and get().

Example: Entry.objects.get(id__exact=14)  # note double underscore.

Protip: Use in to avoid chaining filter() and exclude()

Entry.objects.filter(status__in=['Hung over', 'Sober', 'Drunk'])

Aggregation functions

Query-related classes

Creative Commons License
Django-QuerySet-Cheatsheet by @chrisdl and @briandant is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

The Django web framework referenced in the Django-QuerySet-Cheatsheet is ​© 2005-2015 Django Software Foundation. Django is a registered trademark of the Django Software Foundation.