Expressions¶

Q Expression¶

Sometimes you need to do more complicated queries than the simple AND <model>.filter() provides. Luckily we have Q objects to spice things up and help you find what you need. These Q-objects can then be used as argument to <model>.filter() instead.

Q objects are extremely versatile, some example use cases:

creating an OR filter
nested filters
inverted filters
combining any of the above to simply write complicated multilayer filters

Q objects can take any (special) kwargs for filtering that <model>.filter() accepts, see those docs for a full list of filter options in that regard.

They can also be combined by using bitwise operators (| is OR and & is AND for those unfamiliar with bitwise operators)

For example to find the events with as name Event 1 or Event 2:

found_events = await Event.filter(
    Q(name='Event 1') | Q(name='Event 2')
)

Q objects can be nested as well, the above for example is equivalent to:

found_events = await Event.filter(
    Q(Q(name='Event 1'), Q(name='Event 2'), join_type="OR")
)

If join type is omitted it defaults to AND.

Note

Q objects without filter arguments are considered NOP and will be ignored for the final query (regardless on if they are used as AND or OR param)

Also, Q objects support negated to generate NOT (~ operator) clause in your query

not_third_events = await Event.filter(~Q(name='3'))

class tortoise.expressions.Q(*args, join_type='AND', **kwargs)[source]¶

Q Expression container. Q Expressions are a useful tool to compose a query from many small parts.

Parameters:¶

join_type='AND'¶: Is the join an AND or OR join type?
*args¶: Inner Q expressions that you want to wrap.
**kwargs¶: Filter statements that this Q object should encapsulate.

AND = 'AND'¶

OR = 'OR'¶

__and__(other)[source]¶

Returns a binary AND of Q objects, use AND operator.

Raises:¶: OperationalError – AND operation requires a Q node
Return type:¶: Q

__invert__()[source]¶

Returns a negated instance of the Q object, use ~ operator.

Return type:¶: Q

__or__(other)[source]¶

Returns a binary OR of Q objects, use OR operator.

Raises:¶: OperationalError – OR operation requires a Q node
Return type:¶: Q

children : tuple[Q, ...]¶: Contains the sub-Q’s that this Q is made up of

filters : dict[str, Any]¶: Contains the filters applied to this Q

join_type¶: Specifies if this Q does an AND or OR on its children

negate()[source]¶

Negates the current Q object. (mutation)

Return type:¶: None

resolve(model, table)[source]¶

Resolves the logical Q chain into the parts of a SQL statement.

Parameters:¶

model¶: The Model this Q Expression should be resolved on.
table¶: pypika.Table to keep track of the virtual SQL table (to allow self referential joins)

Return type:¶

QueryModifier

F Expression¶

An F object represents the value of a model field. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.

For example to use F to update user balance atomic:

from tortoise.expressions import F

await User.filter(id=1).update(balance = F('balance') - 10)
await User.filter(id=1).update(balance = F('balance') + F('award'), award = 0)

# or use .save()
user = await User.get(id=1)
user.balance = F('balance') - 10
await user.save(update_fields=['balance'])

For this if you want access updated F field again, you should call refresh_from_db to refresh special fields first.

# Can't do this!
balance = user.balance
await user.refresh_from_db(fields=['balance'])
# Great!
balance = user.balance

And you can also use F in annotate.

data = await User.annotate(idp=F("id") + 1).values_list("id", "idp")

Subquery¶

You can use Subquery in filter() and annotate().

from tortoise.expressions import Subquery

await Tournament.annotate(ids=Subquery(Tournament.all().limit(1).values("id"))).values("ids", "id")
await Tournament.filter(pk=Subquery(Tournament.filter(pk=t1.pk).values("id"))).first()

RawSQL¶

RawSQL just like Subquery but provides the ability to write raw sql.

You can use RawSQL in filter() and annotate().

await Tournament.filter(pk=1).annotate(count=RawSQL('count(*)')).values("count")
await Tournament.filter(pk=1).annotate(idp=RawSQL('id + 1')).filter(idp=2).values("idp")
await Tournament.filter(pk=RawSQL("id + 1"))

Case-When Expression¶

Build classic CASE WHEN … THEN … ELSE … END sql snippet.

class tortoise.expressions.When(*args, then, negate=False, **kwargs)[source]¶

When expression.

Parameters:¶

*args¶: Q objects
**kwargs¶: keyword criterion like filter
then¶: value for criterion
negate=False¶: false (default)

class tortoise.expressions.Case(*args, default=None)[source]¶

Case expression.

Parameters:¶

*args¶: When objects
default=None¶: value for ‘CASE WHEN … THEN … ELSE <default> END’

results = await IntModel.all().annotate(category=Case(When(intnum__gte=8, then='big'), When(intnum__lte=2, then='small'), default='middle'))