0
# Database Functions and Expressions
1

2
Database function support including text functions, aggregate functions, and custom expressions for advanced queries. These functions provide database-level operations that can be used in queries, annotations, and aggregations.
3

4
## Capabilities
5

6
### Function Base Classes
7

8
Base classes for creating and using database functions in queries.
9

10
```python { .api }
11
class Expression:
12
    """Base class for query expressions."""
13
    
14
    def __init__(self, *args, **kwargs):
15
        """
16
        Initialize expression.
17
        
18
        Args:
19
            *args: Expression arguments
20
            **kwargs: Expression options
21
        """
22

23
class Function(Expression):
24
    """Base class for database functions."""
25
    
26
    def __init__(self, *args, **kwargs):
27
        """
28
        Initialize function with arguments.
29
        
30
        Args:
31
            *args: Function arguments (field names, values)
32
            **kwargs: Function options
33
        """
34

35
class Aggregate(Function):
36
    """Base class for aggregate functions."""
37
    
38
    def __init__(self, field, distinct=False, **kwargs):
39
        """
40
        Initialize aggregate function.
41
        
42
        Args:
43
            field (str): Field name to aggregate
44
            distinct (bool): Use DISTINCT in aggregation
45
            **kwargs: Additional options
46
        """
47
```
48

49
### Text Functions
50

51
Functions for manipulating text and string data.
52

53
```python { .api }
54
class Trim:
55
    """Trim whitespace from edges of text."""
56
    def __init__(self, field): ...
57

58
class Length:
59
    """Get length of text or binary data."""
60
    def __init__(self, field): ...
61

62
class Lower:
63
    """Convert text to lowercase."""
64
    def __init__(self, field): ...
65

66
class Upper:
67
    """Convert text to uppercase.""" 
68
    def __init__(self, field): ...
69

70
class Coalesce:
71
    """Return first non-null value from arguments."""
72
    def __init__(self, *fields): ...
73

74
class Concat:
75
    """Concatenate multiple text values."""
76
    def __init__(self, *fields, separator=""): ...
77

78
class Substring:
79
    """Extract substring from text."""
80
    def __init__(self, field, start, length=None): ...
81

82
class Replace:
83
    """Replace occurrences of substring."""
84
    def __init__(self, field, search, replace): ...
85
```
86

87
### Mathematical Functions
88

89
Functions for mathematical operations and calculations.
90

91
```python { .api }
92
class Abs:
93
    """Absolute value of number."""
94
    def __init__(self, field): ...
95

96
class Ceil:
97
    """Ceiling (round up) of number."""
98
    def __init__(self, field): ...
99

100
class Floor:
101
    """Floor (round down) of number."""
102
    def __init__(self, field): ...
103

104
class Round:
105
    """Round number to specified decimal places."""
106
    def __init__(self, field, precision=0): ...
107

108
class Mod:
109
    """Modulo operation."""
110
    def __init__(self, field, divisor): ...
111

112
class Power:
113
    """Raise number to power."""
114
    def __init__(self, field, exponent): ...
115

116
class Sqrt:
117
    """Square root of number."""
118
    def __init__(self, field): ...
119
```
120

121
### Date and Time Functions
122

123
Functions for working with date and time values.
124

125
```python { .api }
126
class Now:
127
    """Current date and time."""
128
    def __init__(self): ...
129

130
class Extract:
131
    """Extract part of date/time value."""
132
    def __init__(self, field, lookup_type):
133
        """
134
        Args:
135
            field (str): Date/time field name
136
            lookup_type (str): Part to extract ('year', 'month', 'day', 'hour', etc.)
137
        """
138

139
class DateDiff:
140
    """Difference between two dates."""
141
    def __init__(self, field1, field2, interval='day'): ...
142

143
class DateAdd:
144
    """Add interval to date."""
145
    def __init__(self, field, interval, amount): ...
146

147
class DateFormat:
148
    """Format date as string."""
149
    def __init__(self, field, format_string): ...
150
```
151

152
### Aggregate Functions
153

154
Functions that compute single values from multiple rows.
155

156
```python { .api }
157
class Count:
158
    """Count number of rows or non-null values."""
159
    def __init__(self, field="*", distinct=False): ...
160

161
class Sum:
162
    """Sum of numeric values."""
163
    def __init__(self, field, distinct=False): ...
164

165
class Avg:
166
    """Average of numeric values."""
167
    def __init__(self, field, distinct=False): ...
168

169
class Max:
170
    """Maximum value."""
171
    def __init__(self, field): ...
172

173
class Min:
174
    """Minimum value."""
175
    def __init__(self, field): ...
176

177
class StdDev:
178
    """Standard deviation."""
179
    def __init__(self, field): ...
180

181
class Variance:
182
    """Variance."""
183
    def __init__(self, field): ...
184
```
185

186
### Conditional Functions
187

188
Functions for conditional logic in queries.
189

190
```python { .api }
191
class Case:
192
    """Conditional CASE expression."""
193
    def __init__(self, *cases, default=None):
194
        """
195
        Args:
196
            *cases: Tuple pairs of (condition, result)
197
            default: Default value if no conditions match
198
        """
199

200
class When:
201
    """Condition for CASE expression."""
202
    def __init__(self, condition, then): ...
203

204
class Greatest:
205
    """Return greatest value from multiple fields."""
206
    def __init__(self, *fields): ...
207

208
class Least:
209
    """Return smallest value from multiple fields."""
210
    def __init__(self, *fields): ...
211
```
212

213
## Usage Examples
214

215
### Text Function Examples
216

217
```python
218
from tortoise.functions import Trim, Length, Lower, Upper, Coalesce, Concat
219

220
class User(Model):
221
    id = fields.IntField(pk=True)
222
    first_name = fields.CharField(max_length=50)
223
    last_name = fields.CharField(max_length=50)
224
    email = fields.CharField(max_length=100)
225
    bio = fields.TextField(null=True)
226

227
# Text manipulation
228
users_with_clean_names = await User.annotate(
229
    clean_first_name=Trim('first_name'),
230
    name_length=Length('first_name'),
231
    lower_email=Lower('email'),
232
    upper_name=Upper('first_name')
233
).all()
234

235
# Concatenation
236
users_with_full_name = await User.annotate(
237
    full_name=Concat('first_name', 'last_name', separator=' ')
238
).all()
239

240
# Handle null values
241
users_with_bio = await User.annotate(
242
    display_bio=Coalesce('bio', 'No bio available')
243
).all()
244
```
245

246
### Mathematical Function Examples
247

248
```python
249
from tortoise.functions import Abs, Round, Mod
250

251
class Product(Model):
252
    id = fields.IntField(pk=True)
253
    name = fields.CharField(max_length=100)
254
    price = fields.DecimalField(max_digits=10, decimal_places=2)
255
    discount = fields.DecimalField(max_digits=5, decimal_places=2, default=0)
256

257
# Mathematical operations
258
products_with_calculations = await Product.annotate(
259
    abs_discount=Abs('discount'),
260
    rounded_price=Round('price', 2),
261
    price_mod=Mod('price', 10)
262
).all()
263
```
264

265
### Date Function Examples
266

267
```python
268
from tortoise.functions import Extract, Now
269

270
class Order(Model):
271
    id = fields.IntField(pk=True)
272
    created_at = fields.DatetimeField(auto_now_add=True)
273
    total = fields.DecimalField(max_digits=10, decimal_places=2)
274

275
# Date operations
276
orders_by_year = await Order.annotate(
277
    year=Extract('created_at', 'year'),
278
    month=Extract('created_at', 'month'),
279
    day=Extract('created_at', 'day')
280
).all()
281

282
# Current time
283
current_time = await Order.annotate(
284
    current_time=Now()
285
).first()
286
```
287

288
### Aggregate Function Examples
289

290
```python
291
from tortoise.functions import Count, Sum, Avg, Max, Min
292

293
class Post(Model):
294
    id = fields.IntField(pk=True)
295
    title = fields.CharField(max_length=200)
296
    author = fields.ForeignKeyField('models.User', related_name='posts')
297
    view_count = fields.IntField(default=0)
298
    created_at = fields.DatetimeField(auto_now_add=True)
299

300
# Simple aggregation
301
stats = await Post.aggregate(
302
    total_posts=Count('id'),
303
    total_views=Sum('view_count'),
304
    avg_views=Avg('view_count'),
305
    max_views=Max('view_count'),
306
    min_views=Min('view_count')
307
)
308

309
# Aggregate with grouping
310
author_stats = await User.annotate(
311
    post_count=Count('posts'),
312
    total_views=Sum('posts__view_count'),
313
    avg_views=Avg('posts__view_count')
314
).filter(post_count__gt=0).all()
315

316
# Distinct aggregation
317
unique_authors = await Post.aggregate(
318
    author_count=Count('author', distinct=True)
319
)
320
```
321

322
### Conditional Function Examples
323

324
```python
325
from tortoise.functions import Case, When
326
from tortoise.queryset import Q
327

328
class User(Model):
329
    id = fields.IntField(pk=True)
330
    age = fields.IntField()
331
    is_premium = fields.BooleanField(default=False)
332
    posts_count = fields.IntField(default=0)
333

334
# Conditional logic
335
users_with_category = await User.annotate(
336
    age_category=Case(
337
        When(Q(age__lt=18), then='Minor'),
338
        When(Q(age__lt=65), then='Adult'),
339
        default='Senior'
340
    ),
341
    user_type=Case(
342
        When(Q(is_premium=True), then='Premium'),
343
        When(Q(posts_count__gt=10), then='Active'),
344
        default='Regular'
345
    )
346
).all()
347

348
# Complex conditions
349
users_with_status = await User.annotate(
350
    status=Case(
351
        When(Q(is_premium=True) & Q(posts_count__gt=50), then='VIP'),
352
        When(Q(is_premium=True), then='Premium'),
353
        When(Q(posts_count__gt=20), then='Active'),
354
        When(Q(posts_count__gt=5), then='Regular'),
355
        default='New'
356
    )
357
).all()
358
```
359

360
### Window Functions
361

362
Advanced analytical functions for complex calculations.
363

364
```python
365
from tortoise.functions import RowNumber, Rank, DenseRank
366

367
class Sale(Model):
368
    id = fields.IntField(pk=True)
369
    product_name = fields.CharField(max_length=100)
370
    amount = fields.DecimalField(max_digits=10, decimal_places=2)
371
    sale_date = fields.DateField()
372
    region = fields.CharField(max_length=50)
373

374
# Window functions (if supported by database)
375
sales_with_ranking = await Sale.annotate(
376
    row_number=RowNumber().over(order_by=['amount']),
377
    rank_by_amount=Rank().over(order_by=['-amount']),
378
    dense_rank=DenseRank().over(
379
        partition_by=['region'],
380
        order_by=['-amount']
381
    )
382
).all()
383
```
384

385
### Custom Functions
386

387
Creating custom database functions for specific needs.
388

389
```python
390
from tortoise.expressions import Function
391

392
class CustomFunction(Function):
393
    """Custom database function."""
394
    database_func = 'CUSTOM_FUNC'  # SQL function name
395
    
396
    def __init__(self, field, param1, param2=None):
397
        super().__init__(field, param1, param2)
398

399
# Usage
400
results = await MyModel.annotate(
401
    custom_result=CustomFunction('my_field', 'param_value')
402
).all()
403
```
404

405
### Function Composition
406

407
Combining multiple functions for complex operations.
408

409
```python
410
from tortoise.functions import Lower, Trim, Length, Concat
411

412
# Compose multiple functions
413
users_processed = await User.annotate(
414
    # Chain text operations
415
    clean_email=Lower(Trim('email')),
416
    
417
    # Use function results in other functions
418
    full_name_length=Length(
419
        Concat('first_name', 'last_name', separator=' ')
420
    ),
421
    
422
    # Nested function calls
423
    formatted_name=Upper(
424
        Trim(
425
            Concat('first_name', 'last_name', separator=' ')
426
        )
427
    )
428
).all()
429
```
430

431
### Database-Specific Functions
432

433
Using database-specific functions through contrib modules.
434

435
```python
436
# PostgreSQL-specific functions
437
from tortoise.contrib.postgres.functions import ArrayAgg, JsonAgg
438

439
# MySQL-specific functions  
440
from tortoise.contrib.mysql.functions import GroupConcat
441

442
# SQLite-specific functions
443
from tortoise.contrib.sqlite.functions import Julianday
444

445
# PostgreSQL array aggregation
446
tags_by_post = await Post.annotate(
447
    tag_names=ArrayAgg('tags__name')
448
).prefetch_related('tags').all()
449

450
# MySQL group concatenation
451
user_emails = await User.aggregate(
452
    all_emails=GroupConcat('email', separator=';')
453
)
454
```