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_tortoise.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_tortoise.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_tortoise 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_tortoise.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