2
41. Tests for select_related()
4
``select_related()`` follows all relationships and pre-caches any foreign key
5
values so that complex trees can be fetched in a single query. However, this
6
isn't always a good idea, so the ``depth`` argument control how many "levels"
7
the select-related behavior will traverse.
10
from django.db import models
12
# Who remembers high school biology?
14
class Domain(models.Model):
15
name = models.CharField(max_length=50)
16
def __unicode__(self):
19
class Kingdom(models.Model):
20
name = models.CharField(max_length=50)
21
domain = models.ForeignKey(Domain)
22
def __unicode__(self):
25
class Phylum(models.Model):
26
name = models.CharField(max_length=50)
27
kingdom = models.ForeignKey(Kingdom)
28
def __unicode__(self):
31
class Klass(models.Model):
32
name = models.CharField(max_length=50)
33
phylum = models.ForeignKey(Phylum)
34
def __unicode__(self):
37
class Order(models.Model):
38
name = models.CharField(max_length=50)
39
klass = models.ForeignKey(Klass)
40
def __unicode__(self):
43
class Family(models.Model):
44
name = models.CharField(max_length=50)
45
order = models.ForeignKey(Order)
46
def __unicode__(self):
49
class Genus(models.Model):
50
name = models.CharField(max_length=50)
51
family = models.ForeignKey(Family)
52
def __unicode__(self):
55
class Species(models.Model):
56
name = models.CharField(max_length=50)
57
genus = models.ForeignKey(Genus)
58
def __unicode__(self):
61
def create_tree(stringtree):
62
"""Helper to create a complete tree"""
63
names = stringtree.split()
64
models = [Domain, Kingdom, Phylum, Klass, Order, Family, Genus, Species]
65
assert len(names) == len(models), (names, models)
68
for name, model in zip(names, models):
70
obj = model.objects.get(name=name)
71
except model.DoesNotExist:
72
obj = model(name=name)
74
setattr(obj, parent.__class__.__name__.lower(), parent)
78
__test__ = {'API_TESTS':"""
81
# The test runner sets settings.DEBUG to False, but we want to gather queries
82
# so we'll set it to True here and reset it at the end of the test suite.
83
>>> from django.conf import settings
84
>>> settings.DEBUG = True
86
>>> create_tree("Eukaryota Animalia Anthropoda Insecta Diptera Drosophilidae Drosophila melanogaster")
87
>>> create_tree("Eukaryota Animalia Chordata Mammalia Primates Hominidae Homo sapiens")
88
>>> create_tree("Eukaryota Plantae Magnoliophyta Magnoliopsida Fabales Fabaceae Pisum sativum")
89
>>> create_tree("Eukaryota Fungi Basidiomycota Homobasidiomycatae Agaricales Amanitacae Amanita muscaria")
91
>>> from django import db
93
# Normally, accessing FKs doesn't fill in related objects:
94
>>> db.reset_queries()
95
>>> fly = Species.objects.get(name="melanogaster")
96
>>> fly.genus.family.order.klass.phylum.kingdom.domain
98
>>> len(db.connection.queries)
101
# However, a select_related() call will fill in those related objects without any extra queries:
102
>>> db.reset_queries()
103
>>> person = Species.objects.select_related(depth=10).get(name="sapiens")
104
>>> person.genus.family.order.klass.phylum.kingdom.domain
106
>>> len(db.connection.queries)
109
# select_related() also of course applies to entire lists, not just items.
110
# Without select_related()
111
>>> db.reset_queries()
112
>>> world = Species.objects.all()
113
>>> [o.genus.family for o in world]
114
[<Family: Drosophilidae>, <Family: Hominidae>, <Family: Fabaceae>, <Family: Amanitacae>]
115
>>> len(db.connection.queries)
118
# With select_related():
119
>>> db.reset_queries()
120
>>> world = Species.objects.all().select_related()
121
>>> [o.genus.family for o in world]
122
[<Family: Drosophilidae>, <Family: Hominidae>, <Family: Fabaceae>, <Family: Amanitacae>]
123
>>> len(db.connection.queries)
126
# The "depth" argument to select_related() will stop the descent at a particular level:
127
>>> db.reset_queries()
128
>>> pea = Species.objects.select_related(depth=1).get(name="sativum")
129
>>> pea.genus.family.order.klass.phylum.kingdom.domain
132
# Notice: one fewer queries than above because of depth=1
133
>>> len(db.connection.queries)
136
>>> db.reset_queries()
137
>>> pea = Species.objects.select_related(depth=5).get(name="sativum")
138
>>> pea.genus.family.order.klass.phylum.kingdom.domain
140
>>> len(db.connection.queries)
143
>>> db.reset_queries()
144
>>> world = Species.objects.all().select_related(depth=2)
145
>>> [o.genus.family.order for o in world]
146
[<Order: Diptera>, <Order: Primates>, <Order: Fabales>, <Order: Agaricales>]
147
>>> len(db.connection.queries)
150
>>> s = Species.objects.all().select_related(depth=1).extra(select={'a': 'select_related_species.id + 10'})[0]
154
# The optional fields passed to select_related() control which related models
155
# we pull in. This allows for smaller queries and can act as an alternative
156
# (or, in addition to) the depth parameter.
158
# In the next two cases, we explicitly say to select the 'genus' and
159
# 'genus.family' models, leading to the same number of queries as before.
160
>>> db.reset_queries()
161
>>> world = Species.objects.select_related('genus__family')
162
>>> [o.genus.family for o in world]
163
[<Family: Drosophilidae>, <Family: Hominidae>, <Family: Fabaceae>, <Family: Amanitacae>]
164
>>> len(db.connection.queries)
167
>>> db.reset_queries()
168
>>> world = Species.objects.filter(genus__name='Amanita').select_related('genus__family')
169
>>> [o.genus.family.order for o in world]
170
[<Order: Agaricales>]
171
>>> len(db.connection.queries)
174
>>> db.reset_queries()
175
>>> Species.objects.all().select_related('genus__family__order').order_by('id')[0:1].get().genus.family.order.name
177
>>> len(db.connection.queries)
180
# Specifying both "depth" and fields is an error.
181
>>> Species.objects.select_related('genus__family__order', depth=4)
182
Traceback (most recent call last):
184
TypeError: Cannot pass both "depth" and fields to select_related()
186
# Reset DEBUG to where we found it.
187
>>> settings.DEBUG = False