Functions & Aggregates¶

To apply functions to values and get aggregates computed on the DB side, one needs to annotate the QuerySet.

results = await SomeModel.filter(...).annotate(clean_desc=Coalesce("desc", "N/A"))

This will add a new attribute on each SomeModel instance called clean_desc that will now contain the annotated data.

One can also call .values() or .values_list() on it to get the data as per regular.

Functions¶

Functions apply a transform on each instance of a Field.

class tortoise.functions.Trim(field, *default_values)[source]¶

Trims whitespace off edges of text.

Trim("FIELD_NAME")

class tortoise.functions.Length(field, *default_values)[source]¶

Returns length of text/blob.

Length("FIELD_NAME")

class tortoise.functions.Coalesce(field, *default_values)[source]¶

Provides a default value if field is null.

Coalesce("FIELD_NAME", DEFAULT_VALUE)

class tortoise.functions.Lower(field, *default_values)[source]¶

Converts text to lower case.

Lower("FIELD_NAME")

class tortoise.functions.Upper(field, *default_values)[source]¶

Converts text to upper case.

Upper("FIELD_NAME")

class tortoise.functions.Concat(field, *default_values)[source]¶: Concate field or constant text. Be care, DB like sqlite3 has no support for CONCAT.

Concat("FIELD_NAME", ANOTHER_FIELD_NAMES or CONSTANT_TEXT, *args)

class tortoise.contrib.mysql.functions.Rand(seed=None, alias=None)[source]¶

Generate random number, with optional seed.

Rand()

class tortoise.contrib.postgres.functions.Random(alias=None)[source]¶

Generate random number.

Random()

class tortoise.contrib.sqlite.functions.Random(alias=None)[source]¶

Generate random number.

Random()

Aggregates¶

Aggregated apply on the entire column, and will often be used with grouping. So often makes sense with a .first() QuerySet.

class tortoise.functions.Count(field, *default_values, distinct=False, _filter=None)[source]¶

Counts the no of entries for that column.

Count("FIELD_NAME")

class tortoise.functions.Sum(field, *default_values, distinct=False, _filter=None)[source]¶

Adds up all the values for that column.

Sum("FIELD_NAME")

class tortoise.functions.Max(field, *default_values, distinct=False, _filter=None)[source]¶

Returns largest value in the column.

Max("FIELD_NAME")

class tortoise.functions.Min(field, *default_values, distinct=False, _filter=None)[source]¶

Returns smallest value in the column.

Min("FIELD_NAME")

class tortoise.functions.Avg(field, *default_values, distinct=False, _filter=None)[source]¶

Returns average (mean) of all values in the column.

Avg("FIELD_NAME")

Base function class¶

class tortoise.functions.Function(field, *default_values)[source]¶

Function/Aggregate base.

Parameters:¶

field¶: Field name
*default_values¶: Extra parameters to the function.

database_func pypika.terms.Function¶: The pypika function this represents.

populate_field_object bool = False¶: Enable populate_field_object where we want to try and preserve the field type.

resolve(resolve_context)[source]¶

Used to resolve the Function statement for SQL generation.

Parameters:¶

model: Model the function is applied on to.
table: pypika.Table to keep track of the virtual SQL table (to allow self referential joins)

Return type:¶

ResolveResult

Returns:¶

Dict with keys "joins" and "fields"

class tortoise.functions.Aggregate(field, *default_values, distinct=False, _filter=None)[source]¶

Base for SQL Aggregates.

Parameters:¶

field¶: Field name
*default_values¶: Extra parameters to the function.
is_distinct: Flag for aggregate with distinction

Custom functions¶

You can define custom functions which are not builtin, such as TruncMonth and JsonExtract etc.

from pypika import CustomFunction
from tortoise.expressions import F, Function

class TruncMonth(Function):
    database_func = CustomFunction("DATE_FORMAT", ["name", "dt_format"])

sql = Task.all().annotate(date=TruncMonth('created_at', '%Y-%m-%d')).values('date').sql()
print(sql)
# SELECT DATE_FORMAT(`created_at`,'%Y-%m-%d') `date` FROM `task`

And you can also use functions in update, the example is only suitable for MySQL and SQLite, but PostgreSQL is the same.

from tortoise.expressions import F
from tortoise.functions import Function
from pypika.terms import Function as PupikaFunction

class JsonSet(Function):
    class PypikaJsonSet(PupikaFunction):
        def __init__(self, field: F, expression: str, value: Any):
            super().__init__("JSON_SET", field, expression, value)

    database_func = PypikaJsonSet

json = await JSONFields.create(data_default={"a": 1})
json.data_default = JsonSet(F("data_default"), "$.a", 2)
await json.save()

# or use queryset.update()
sql = JSONFields.filter(pk=json.pk).update(data_default=JsonSet(F("data_default"), "$.a", 3)).sql()
print(sql)
# UPDATE jsonfields SET data_default=JSON_SET(`data_default`,'$.a',3) where id=1