0
# Querying and Filtering
1
2
Advanced query building capabilities with QuerySet, Q expressions, filtering, ordering, aggregation, and bulk operations for efficient database access. Tortoise ORM provides a rich query API similar to Django's ORM but optimized for async operations.
3
4
## Capabilities
5
6
### QuerySet Operations
7
8
Lazy query builder that supports method chaining for complex database queries.
9
10
```python { .api }
11
class QuerySet:
12
"""Lazy query builder for database queries."""
13
14
def filter(self, **kwargs):
15
"""
16
Filter queryset by given criteria.
17
18
Args:
19
**kwargs: Field lookups (field__lookup=value)
20
21
Returns:
22
QuerySet: Filtered queryset
23
"""
24
25
def exclude(self, **kwargs):
26
"""
27
Exclude records matching criteria.
28
29
Args:
30
**kwargs: Field lookups to exclude
31
32
Returns:
33
QuerySet: Filtered queryset
34
"""
35
36
def order_by(self, *fields):
37
"""
38
Order queryset by given fields.
39
40
Args:
41
*fields: Field names, prefix with '-' for descending
42
43
Returns:
44
QuerySet: Ordered queryset
45
"""
46
47
def limit(self, limit):
48
"""
49
Limit number of results.
50
51
Args:
52
limit (int): Maximum number of results
53
54
Returns:
55
QuerySet: Limited queryset
56
"""
57
58
def offset(self, offset):
59
"""
60
Skip number of results.
61
62
Args:
63
offset (int): Number of results to skip
64
65
Returns:
66
QuerySet: Offset queryset
67
"""
68
69
def distinct(self):
70
"""
71
Return distinct results.
72
73
Returns:
74
QuerySet: Distinct queryset
75
"""
76
77
def select_related(self, *fields):
78
"""
79
Follow foreign keys and select related data.
80
81
Args:
82
*fields: Related field names to select
83
84
Returns:
85
QuerySet: Queryset with related data
86
"""
87
88
def prefetch_related(self, *fields):
89
"""
90
Prefetch related objects in separate queries.
91
92
Args:
93
*fields: Related field names to prefetch
94
95
Returns:
96
QuerySet: Queryset with prefetched data
97
"""
98
99
def annotate(self, **kwargs):
100
"""
101
Add annotations to queryset.
102
103
Args:
104
**kwargs: Annotations (name=expression)
105
106
Returns:
107
QuerySet: Annotated queryset
108
"""
109
110
def group_by(self, *fields):
111
"""
112
Group results by fields.
113
114
Args:
115
*fields: Field names to group by
116
117
Returns:
118
QuerySet: Grouped queryset
119
"""
120
121
async def all(self):
122
"""
123
Get all results as a list.
124
125
Returns:
126
list: All matching model instances
127
"""
128
129
async def first(self):
130
"""
131
Get first result or None.
132
133
Returns:
134
Model or None: First matching instance
135
"""
136
137
async def get(self, **kwargs):
138
"""
139
Get single result matching criteria.
140
141
Args:
142
**kwargs: Filter criteria
143
144
Returns:
145
Model: Matching model instance
146
147
Raises:
148
DoesNotExist: If no match found
149
MultipleObjectsReturned: If multiple matches found
150
"""
151
152
async def get_or_none(self, **kwargs):
153
"""
154
Get single result or None.
155
156
Args:
157
**kwargs: Filter criteria
158
159
Returns:
160
Model or None: Matching instance or None
161
"""
162
163
async def count(self):
164
"""
165
Count matching results.
166
167
Returns:
168
int: Number of matching records
169
"""
170
171
async def exists(self):
172
"""
173
Check if any results exist.
174
175
Returns:
176
bool: True if results exist
177
"""
178
179
async def delete(self):
180
"""
181
Delete all matching records.
182
183
Returns:
184
int: Number of deleted records
185
"""
186
187
async def update(self, **kwargs):
188
"""
189
Update all matching records.
190
191
Args:
192
**kwargs: Fields to update
193
194
Returns:
195
int: Number of updated records
196
"""
197
```
198
199
### Q Expressions
200
201
Complex query expressions for advanced filtering with AND, OR, and NOT operations.
202
203
```python { .api }
204
class Q:
205
"""Query expression for complex conditions."""
206
207
def __init__(self, **kwargs):
208
"""
209
Create Q expression.
210
211
Args:
212
**kwargs: Field lookups
213
"""
214
215
def __and__(self, other):
216
"""AND operation with another Q expression."""
217
218
def __or__(self, other):
219
"""OR operation with another Q expression."""
220
221
def __invert__(self):
222
"""NOT operation (negation)."""
223
224
# Usage with Q expressions
225
from tortoise.queryset import Q
226
227
# Complex conditions
228
queryset = Model.filter(
229
Q(name__startswith='A') | Q(age__gte=18)
230
)
231
232
queryset = Model.filter(
233
Q(status='active') & ~Q(name__in=['admin', 'root'])
234
)
235
```
236
237
### Field Lookups
238
239
Available field lookup types for filtering and querying.
240
241
```python { .api }
242
# Exact match
243
Model.filter(name='Alice')
244
Model.filter(name__exact='Alice')
245
246
# Case-insensitive exact match
247
Model.filter(name__iexact='alice')
248
249
# Contains
250
Model.filter(name__contains='Ali')
251
Model.filter(name__icontains='ali') # Case-insensitive
252
253
# Starts with / Ends with
254
Model.filter(name__startswith='A')
255
Model.filter(name__istartswith='a') # Case-insensitive
256
Model.filter(name__endswith='e')
257
Model.filter(name__iendswith='E') # Case-insensitive
258
259
# Numeric comparisons
260
Model.filter(age__gt=18) # Greater than
261
Model.filter(age__gte=18) # Greater than or equal
262
Model.filter(age__lt=65) # Less than
263
Model.filter(age__lte=65) # Less than or equal
264
265
# Range
266
Model.filter(age__range=[18, 65])
267
268
# In list
269
Model.filter(status__in=['active', 'pending'])
270
271
# Null checks
272
Model.filter(name__isnull=True)
273
Model.filter(name__not_isnull=True)
274
275
# Date/time lookups
276
Model.filter(created_at__year=2023)
277
Model.filter(created_at__month=12)
278
Model.filter(created_at__day=25)
279
Model.filter(created_at__date='2023-12-25')
280
281
# Related field lookups
282
Model.filter(author__name='Alice')
283
Model.filter(author__posts__count__gt=5)
284
```
285
286
### Bulk Operations
287
288
Efficient operations for handling multiple records.
289
290
```python { .api }
291
class BulkCreateQuery:
292
"""Bulk create query for inserting multiple records."""
293
294
async def bulk_create(self, objects, batch_size=None, ignore_conflicts=False):
295
"""
296
Create multiple model instances efficiently.
297
298
Args:
299
objects (list): List of model instances to create
300
batch_size (int, optional): Number of objects per batch
301
ignore_conflicts (bool): Ignore constraint conflicts
302
303
Returns:
304
list: Created model instances
305
"""
306
307
class BulkUpdateQuery:
308
"""Bulk update query for updating multiple records."""
309
310
async def bulk_update(self, objects, fields, batch_size=None):
311
"""
312
Update multiple model instances efficiently.
313
314
Args:
315
objects (list): List of model instances to update
316
fields (list): Fields to update
317
batch_size (int, optional): Number of objects per batch
318
319
Returns:
320
int: Number of updated records
321
"""
322
323
# Usage
324
users = [User(name=f'User {i}') for i in range(100)]
325
await User.bulk_create(users)
326
327
# Bulk update
328
users = await User.all()
329
for user in users:
330
user.status = 'active'
331
await User.bulk_update(users, ['status'])
332
```
333
334
### Raw SQL Queries
335
336
Execute raw SQL queries when ORM queries are insufficient.
337
338
```python { .api }
339
class RawSQLQuery:
340
"""Raw SQL query executor."""
341
342
@classmethod
343
async def raw(cls, sql, values=None):
344
"""
345
Execute raw SQL query.
346
347
Args:
348
sql (str): SQL query string
349
values (list, optional): Query parameters
350
351
Returns:
352
list: Query results
353
"""
354
355
# Usage
356
results = await User.raw('SELECT * FROM users WHERE age > ?', [18])
357
```
358
359
### Aggregation
360
361
Aggregate functions for computing values across multiple records.
362
363
```python { .api }
364
# Count
365
count = await User.all().count()
366
active_count = await User.filter(is_active=True).count()
367
368
# Aggregate with annotations
369
from tortoise.functions import Count, Sum, Avg, Max, Min
370
371
# Annotate with aggregates
372
users_with_post_count = await User.annotate(
373
post_count=Count('posts')
374
).all()
375
376
# Aggregate values
377
stats = await Post.aggregate(
378
total_posts=Count('id'),
379
avg_views=Avg('view_count'),
380
max_views=Max('view_count'),
381
min_views=Min('view_count')
382
)
383
```
384
385
## Usage Examples
386
387
### Basic Filtering
388
389
```python
390
from tortoise.models import Model
391
from tortoise import fields
392
393
class User(Model):
394
id = fields.IntField(pk=True)
395
name = fields.CharField(max_length=50)
396
email = fields.CharField(max_length=100)
397
age = fields.IntField()
398
is_active = fields.BooleanField(default=True)
399
created_at = fields.DatetimeField(auto_now_add=True)
400
401
# Simple filters
402
active_users = await User.filter(is_active=True).all()
403
adult_users = await User.filter(age__gte=18).all()
404
alice_users = await User.filter(name__icontains='alice').all()
405
406
# Chaining filters
407
recent_adult_users = await User.filter(
408
age__gte=18,
409
created_at__gte=datetime(2023, 1, 1)
410
).order_by('-created_at').limit(10).all()
411
```
412
413
### Complex Queries with Q
414
415
```python
416
from tortoise.queryset import Q
417
418
# OR conditions
419
young_or_senior = await User.filter(
420
Q(age__lt=25) | Q(age__gt=65)
421
).all()
422
423
# AND with NOT
424
active_non_admin = await User.filter(
425
Q(is_active=True) & ~Q(name__in=['admin', 'root'])
426
).all()
427
428
# Complex nested conditions
429
complex_query = await User.filter(
430
(Q(name__startswith='A') | Q(name__startswith='B')) &
431
Q(age__gte=18) &
432
~Q(email__endswith='.temp')
433
).all()
434
```
435
436
### Relationships and Joins
437
438
```python
439
class Post(Model):
440
id = fields.IntField(pk=True)
441
title = fields.CharField(max_length=200)
442
author = fields.ForeignKeyField('models.User', related_name='posts')
443
tags = fields.ManyToManyField('models.Tag', related_name='posts')
444
445
# Select related (JOIN)
446
posts_with_authors = await Post.select_related('author').all()
447
for post in posts_with_authors:
448
print(f"{post.title} by {post.author.name}") # No additional query
449
450
# Prefetch related (separate queries)
451
posts_with_tags = await Post.prefetch_related('tags').all()
452
for post in posts_with_tags:
453
for tag in post.tags: # No additional queries
454
print(tag.name)
455
456
# Filter by related fields
457
python_posts = await Post.filter(tags__name='python').all()
458
alice_posts = await Post.filter(author__name='Alice').all()
459
```
460
461
### Aggregation Examples
462
463
```python
464
from tortoise.functions import Count, Sum, Avg
465
466
# Simple aggregation
467
total_users = await User.all().count()
468
active_users = await User.filter(is_active=True).count()
469
470
# Annotations
471
users_with_post_count = await User.annotate(
472
post_count=Count('posts')
473
).filter(post_count__gt=5).all()
474
475
# Group by with aggregation
476
tag_stats = await Tag.annotate(
477
post_count=Count('posts')
478
).filter(post_count__gt=0).order_by('-post_count').all()
479
480
# Multiple aggregates
481
post_stats = await Post.aggregate(
482
total_posts=Count('id'),
483
unique_authors=Count('author', distinct=True)
484
)
485
```
486
487
### Bulk Operations
488
489
```python
490
# Bulk create
491
users_data = [
492
{'name': f'User {i}', 'email': f'user{i}@example.com', 'age': 20 + i}
493
for i in range(1000)
494
]
495
users = [User(**data) for data in users_data]
496
created_users = await User.bulk_create(users, batch_size=100)
497
498
# Bulk update
499
await User.filter(is_active=False).update(is_active=True)
500
501
# Bulk delete
502
await User.filter(created_at__lt=datetime(2020, 1, 1)).delete()
503
```
504
505
### Pagination
506
507
```python
508
# Manual pagination
509
page_size = 20
510
page_number = 1
511
512
users = await User.filter(is_active=True).order_by('name').offset(
513
(page_number - 1) * page_size
514
).limit(page_size).all()
515
516
total_count = await User.filter(is_active=True).count()
517
has_next = total_count > page_number * page_size
518
```
519
520
### Raw SQL
521
522
```python
523
# Raw query returning model instances
524
users = await User.raw(
525
'SELECT * FROM users WHERE age > ? AND name LIKE ?',
526
[18, 'A%']
527
)
528
529
# Raw query with connection
530
from tortoise import connections
531
conn = connections.get('default')
532
result = await conn.execute_query(
533
'SELECT COUNT(*) as count FROM users WHERE is_active = ?',
534
[True]
535
)
536
```