0
# Database Query Optimization
1
2
Mixins for optimizing database queries in Django class-based views through select_related, prefetch_related, and dynamic ordering. These mixins help reduce database queries and improve performance in list and detail views.
3
4
## Capabilities
5
6
### Select Related Optimization
7
8
Automatically apply select_related to reduce database queries for foreign key relationships.
9
10
```python { .api }
11
class SelectRelatedMixin:
12
"""Automatically apply select_related for list of relations"""
13
select_related = None
14
15
def get_queryset(self):
16
"""Apply select_related with appropriate fields to queryset"""
17
```
18
19
Usage example:
20
21
```python
22
from django.views.generic import ListView
23
from braces.views import SelectRelatedMixin
24
25
class OptimizedPostListView(SelectRelatedMixin, ListView):
26
model = Post
27
select_related = ['author', 'category', 'author__profile']
28
29
# This will generate:
30
# Post.objects.select_related('author', 'category', 'author__profile')
31
```
32
33
Performance comparison:
34
35
```python
36
# Without SelectRelatedMixin - N+1 queries
37
class SlowPostListView(ListView):
38
model = Post
39
# Template access to {{ post.author.name }} causes additional query per post
40
41
# With SelectRelatedMixin - Single query
42
class FastPostListView(SelectRelatedMixin, ListView):
43
model = Post
44
select_related = ['author', 'category']
45
# All related data fetched in one query
46
```
47
48
### Prefetch Related Optimization
49
50
Automatically apply prefetch_related for many-to-many and reverse foreign key relationships.
51
52
```python { .api }
53
class PrefetchRelatedMixin:
54
"""Automatically apply prefetch_related for list of relations"""
55
prefetch_related = None
56
57
def get_queryset(self):
58
"""Apply prefetch_related with appropriate fields to queryset"""
59
```
60
61
Usage example:
62
63
```python
64
from django.views.generic import ListView
65
from braces.views import PrefetchRelatedMixin
66
67
class TaggedPostListView(PrefetchRelatedMixin, ListView):
68
model = Post
69
prefetch_related = ['tags', 'comments', 'comments__author']
70
71
# This will generate:
72
# Post.objects.prefetch_related('tags', 'comments', 'comments__author')
73
```
74
75
Complex prefetching with Prefetch objects:
76
77
```python
78
from django.db.models import Prefetch
79
from django.views.generic import ListView
80
from braces.views import PrefetchRelatedMixin
81
82
class AdvancedPostListView(PrefetchRelatedMixin, ListView):
83
model = Post
84
85
def get_queryset(self):
86
# Custom prefetch with filtering
87
active_comments = Prefetch(
88
'comments',
89
queryset=Comment.objects.filter(is_active=True).select_related('author')
90
)
91
92
return super().get_queryset().prefetch_related(
93
active_comments,
94
'tags',
95
'author__groups'
96
)
97
```
98
99
### Dynamic List Ordering
100
101
Enable URL-based sorting for list views with validation and defaults.
102
103
```python { .api }
104
class OrderableListMixin:
105
"""Order queryset based on GET parameters"""
106
orderable_columns = None
107
orderable_columns_default = None
108
ordering_default = None
109
order_by = None
110
ordering = None
111
112
def get_context_data(self, **kwargs):
113
"""Augment context with order_by and ordering"""
114
115
def get_orderable_columns(self):
116
"""Check that orderable columns are set and return them"""
117
118
def get_orderable_columns_default(self):
119
"""Which column(s) should be sorted by default"""
120
121
def get_ordering_default(self):
122
"""Which direction should things be sorted"""
123
124
def get_ordered_queryset(self, queryset=None):
125
"""Augment QuerySet with order_by statement if possible"""
126
127
def get_queryset(self):
128
"""Returns ordered QuerySet"""
129
```
130
131
Usage example:
132
133
```python
134
from django.views.generic import ListView
135
from braces.views import OrderableListMixin
136
137
class SortablePostListView(OrderableListMixin, ListView):
138
model = Post
139
orderable_columns = ['title', 'created', 'author__username', 'category__name']
140
orderable_columns_default = 'created'
141
ordering_default = 'desc'
142
143
# URLs:
144
# /posts/ - Default: ordered by created desc
145
# /posts/?order_by=title&ordering=asc - Title ascending
146
# /posts/?order_by=author__username - Author username desc (default)
147
```
148
149
Template usage:
150
151
```html
152
<!-- Template: post_list.html -->
153
<table>
154
<thead>
155
<tr>
156
<th>
157
<a href="?order_by=title&ordering={% if order_by == 'title' and ordering == 'asc' %}desc{% else %}asc{% endif %}">
158
Title
159
{% if order_by == 'title' %}
160
{% if ordering == 'asc' %}↑{% else %}↓{% endif %}
161
{% endif %}
162
</a>
163
</th>
164
<th>
165
<a href="?order_by=created&ordering={% if order_by == 'created' and ordering == 'asc' %}desc{% else %}asc{% endif %}">
166
Created
167
{% if order_by == 'created' %}
168
{% if ordering == 'asc' %}↑{% else %}↓{% endif %}
169
{% endif %}
170
</a>
171
</th>
172
</tr>
173
</thead>
174
<tbody>
175
{% for post in object_list %}
176
<tr>
177
<td>{{ post.title }}</td>
178
<td>{{ post.created }}</td>
179
</tr>
180
{% endfor %}
181
</tbody>
182
</table>
183
```
184
185
## Common Usage Patterns
186
187
### Combined Query Optimization
188
189
Combine select_related and prefetch_related for maximum efficiency:
190
191
```python
192
from django.views.generic import ListView
193
from braces.views import SelectRelatedMixin, PrefetchRelatedMixin
194
195
class FullyOptimizedListView(SelectRelatedMixin, PrefetchRelatedMixin, ListView):
196
model = Post
197
select_related = ['author', 'category'] # Foreign keys
198
prefetch_related = ['tags', 'comments'] # Many-to-many and reverse FKs
199
```
200
201
### Sortable Optimized Lists
202
203
Combine ordering with query optimization:
204
205
```python
206
from django.views.generic import ListView
207
from braces.views import SelectRelatedMixin, OrderableListMixin
208
209
class SortableOptimizedListView(SelectRelatedMixin, OrderableListMixin, ListView):
210
model = Post
211
select_related = ['author', 'category']
212
orderable_columns = ['title', 'created', 'author__username', 'category__name']
213
orderable_columns_default = 'created'
214
ordering_default = 'desc'
215
```
216
217
### Advanced Query Optimization
218
219
Custom queryset modifications with optimization mixins:
220
221
```python
222
from django.views.generic import ListView
223
from braces.views import SelectRelatedMixin, PrefetchRelatedMixin
224
from django.db.models import Count, Prefetch
225
226
class AdvancedOptimizedListView(SelectRelatedMixin, PrefetchRelatedMixin, ListView):
227
model = Post
228
select_related = ['author', 'category']
229
230
def get_queryset(self):
231
# Get base optimized queryset
232
queryset = super().get_queryset()
233
234
# Add annotations
235
queryset = queryset.annotate(
236
comment_count=Count('comments'),
237
tag_count=Count('tags')
238
)
239
240
# Add custom prefetch
241
active_comments = Prefetch(
242
'comments',
243
queryset=Comment.objects.filter(is_active=True).order_by('-created')
244
)
245
246
return queryset.prefetch_related(active_comments)
247
248
def get_context_data(self, **kwargs):
249
context = super().get_context_data(**kwargs)
250
251
# Additional context using optimized queries
252
context['total_posts'] = self.get_queryset().count()
253
context['categories'] = Category.objects.annotate(
254
post_count=Count('posts')
255
).order_by('name')
256
257
return context
258
```
259
260
### Performance Monitoring
261
262
Track query performance with debugging:
263
264
```python
265
import logging
266
from django.views.generic import ListView
267
from braces.views import SelectRelatedMixin, PrefetchRelatedMixin
268
from django.db import connection
269
270
logger = logging.getLogger(__name__)
271
272
class MonitoredOptimizedListView(SelectRelatedMixin, PrefetchRelatedMixin, ListView):
273
model = Post
274
select_related = ['author', 'category']
275
prefetch_related = ['tags']
276
277
def get_queryset(self):
278
initial_queries = len(connection.queries)
279
queryset = super().get_queryset()
280
281
if settings.DEBUG:
282
query_count = len(connection.queries) - initial_queries
283
logger.info(f"Queryset generation used {query_count} queries")
284
285
return queryset
286
287
def get_context_data(self, **kwargs):
288
initial_queries = len(connection.queries)
289
context = super().get_context_data(**kwargs)
290
291
if settings.DEBUG:
292
query_count = len(connection.queries) - initial_queries
293
logger.info(f"Context generation used {query_count} queries")
294
295
return context
296
```
297
298
### Conditional Optimization
299
300
Apply different optimizations based on context:
301
302
```python
303
from django.views.generic import ListView
304
from braces.views import SelectRelatedMixin, PrefetchRelatedMixin, OrderableListMixin
305
306
class ConditionalOptimizedListView(
307
SelectRelatedMixin,
308
PrefetchRelatedMixin,
309
OrderableListMixin,
310
ListView
311
):
312
model = Post
313
orderable_columns = ['title', 'created', 'author__username']
314
orderable_columns_default = 'created'
315
316
def get_select_related(self):
317
# Always optimize author
318
relations = ['author']
319
320
# Add category if sorting by it
321
if self.request.GET.get('order_by', '').startswith('category'):
322
relations.append('category')
323
324
return relations
325
326
def get_prefetch_related(self):
327
relations = []
328
329
# Only prefetch tags if user wants to see them
330
if self.request.GET.get('show_tags'):
331
relations.append('tags')
332
333
# Prefetch comments for staff users
334
if self.request.user.is_staff:
335
relations.append('comments')
336
337
return relations
338
339
def get_queryset(self):
340
# Set dynamic relations
341
self.select_related = self.get_select_related()
342
self.prefetch_related = self.get_prefetch_related()
343
344
return super().get_queryset()
345
```