Expressions¶

Q Expression¶

The Q Expression provides advanced querying capabilities beyond the basic filtering provided by <model>.filter(). Q objects enable complex query construction and can be used as arguments to <model>.filter().

Key features of Q objects include:

• Construction of OR conditions

• Creation of nested filters

• Filter inversion

• Combination of multiple conditions into complex queries

Q objects accept any filtering parameters that <model>.filter() supports. Please refer to Query API for a complete list of available options.

Q objects can be combined using bitwise operators:

• | for OR operations

• & for AND operations

Example of using Q objects to query events with specific names:

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

Q objects also support nesting. The following example is equivalent to the previous one:

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

If the join_type parameter is not specified, it defaults to “AND”.

The NOT operation can be achieved using the negation operator (~):

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, FilterInfoDict]¶

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(resolve_context)[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_tortoise.Table to keep track of the virtual SQL table (to allow self referential joins)

Return type:¶

QueryModifier

F Expression¶

The F Expression represents a model field value and enables database operations on field values without loading them into Python memory. This is particularly useful for atomic operations.

Example of using F expressions for balance updates (this is just an example and such updates are not recommended for use in financial applications):

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)

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

When working with F expressions, you must refresh the model instance to access updated field values:

# Incorrect - balance value may be stale
balance = user.balance

# Correct - refresh the balance field first
await user.refresh_from_db(fields=['balance'])
balance = user.balance

F expressions can also be used in annotations:

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

Subquery¶

Subquery expressions can be utilized in both filter() and annotate() operations:

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 provides the capability to execute raw SQL queries within filter() and annotate() operations. This offers maximum flexibility when needed:

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¶

The Case-When expression enables the construction of conditional logic using CASE WHEN ... THEN ... ELSE ... END SQL statements.

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’

Example usage:

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