Query Expressions and Aggregates

Query expressions in this codebase provide a way to describe database-side computations using Python objects. Instead of fetching data to process it in Python, these classes allow the construction of complex SQL fragments for filters, annotations, and updates.

The implementation centers on a hierarchy starting from BaseExpression, which defines the lifecycle of an expression from its definition in a QuerySet to its compilation into SQL.

The Expression Foundation

The core of the expression system is built on three primary classes in django/db/models/expressions.py:

1. BaseExpression: The root class that defines the interface for all expressions. It handles metadata like filterable (whether it can be used in a WHERE clause) and contains_aggregate.
2. Combinable: A mixin that enables Python operators to generate database expressions. For example, it implements __add__, __sub__, and bitwise methods like bitand().
3. Expression: Inherits from both of the above, serving as the standard base for any expression that can be combined with others.

Operator Overloading via Combinable

The Combinable class allows developers to write F('price') + F('tax') instead of manual SQL. When an operator is used, Combinable._combine is called, which returns a CombinedExpression.

# django/db/models/expressions.py

class Combinable:
    ADD = "+"
    SUB = "-"
    # ... other connectors

    def __add__(self, other):
        return self._combine(other, self.ADD, False)

    def _combine(self, other, connector, reversed):
        if not hasattr(other, "resolve_expression"):
            other = Value(other)
        # Returns a CombinedExpression (not shown in detail here)
        if reversed:
            return CombinedExpression(other, connector, self)
        return CombinedExpression(self, connector, other)

Functional Expressions (Func)

The Func class represents a database function call. It uses a template string to define how the function and its arguments are rendered into SQL.

# django/db/models/expressions.py

class Func(SQLiteNumericMixin, Expression):
    function = None
    template = "%(function)s(%(expressions)s)"
    arg_joiner = ", "

    def as_sql(self, compiler, connection, **extra_context):
        # ... logic to compile source expressions
        data = {**self.extra, **extra_context}
        data.setdefault("function", self.function)
        data["expressions"] = data["field"] = arg_joiner.join(sql_parts)
        return template % data, tuple(params)

Subclasses of Func (often found in django.db.models.functions) define specific function names like COALESCE or LOWER.

Aggregates

The Aggregate class, located in django/db/models/aggregates.py, is a specialized Func designed for operations like SUM, AVG, and COUNT. It introduces several database-specific features:

• filter: Allows for conditional aggregation (e.g., Sum('amount', filter=Q(status='paid'))).
• distinct: Adds the DISTINCT keyword inside the function call.
• order_by: Supports ordering within the aggregate, which is required for certain functions in specific databases.

The Aggregate.as_sql method handles the complexity of rendering these clauses, falling back to a CASE statement if the database backend does not natively support the FILTER clause:

# django/db/models/aggregates.py

def as_sql(self, compiler, connection, **extra_context):
    # ...
    if self.filter is not None:
        try:
            filter_sql, filter_params = compiler.compile(self.filter)
        except NotSupportedError:
            # Fallback to CASE statement for backends without FILTER support
            copy = self.copy()
            copy.filter = None
            source_expressions = copy.get_source_expressions()
            condition = When(self.filter.condition, then=source_expressions[0])
            copy.set_source_expressions([Case(condition)] + source_expressions[1:])
            return copy.as_sql(compiler, connection, **extra_context)
    # ...

Conditional Expressions (Case and When)

The Case class implements SQL CASE statements, allowing for branching logic directly in the query. It works in tandem with When objects.

# Example usage found in tests/expressions_case/tests.py
Case(
    When(integer=1, then=Value("one")),
    When(integer=2, then=Value("two")),
    default=Value("other"),
)

The Case.as_sql implementation iterates through its cases (the When objects) and compiles them into a single SQL string. If no cases match and no default is provided, it handles the resulting NULL or default value logic.

Subqueries and Outer References

The Subquery class allows a QuerySet to be used as an expression within another query. This is often used for complex annotations where a value must be pulled from a related table that isn't directly joined.

• Subquery: Wraps a queryset and ensures it is rendered as a nested SELECT.
• OuterRef: Used inside a Subquery to refer to a field in the outer query.

When Subquery.resolve_expression is called, it marks the inner query as a subquery and ensures that any OuterRef instances can correctly resolve their field names against the outer query's model.

Window Functions

The Window class implements the SQL OVER clause for analytical functions. Unlike standard aggregates, window functions do not group the result set into a single row; they perform calculations across a set of table rows that are related to the current row.

Key components of a Window expression:

• expression: Usually an aggregate (like Sum) or a window function (like Rank).
• partition_by: Defines the groups (partitions) the function is applied to.
• order_by: Defines the order of rows within each partition.
• frame: Defines the subset of rows within the partition to include (e.g., "rows between unbounded preceding and current row").

# django/db/models/expressions.py

class Window(SQLiteNumericMixin, Expression):
    template = "%(expression)s OVER (%(window)s)"
    contains_aggregate = False  # Window functions don't trigger GROUP BY
    contains_over_clause = True

    def as_sql(self, compiler, connection, template=None):
        # Compiles PARTITION BY, ORDER BY, and frame into the window_sql list
        # ...
        return (
            template % {"expression": expr_sql, "window": " ".join(window_sql).strip()},
            (*params, *window_params),
        )

Note: Window expressions are typically used in annotate() calls. As defined by BaseExpression.filterable = True (inherited), they are technically allowed in some contexts, but most database backends prohibit window functions in WHERE clauses. This codebase relies on the database backend to raise a NotSupportedError if used incorrectly.