Common Table Expressions (WITH)
Relevant source files
The following files were used as context for generating this wiki page:
Common Table Expressions (CTEs), often referred to as the WITH clause, provide a way to define temporary result sets that can be referenced within the execution scope of a larger query. In SeaQuery, CTEs are part of the statement building logic, allowing developers to construct complex, modular, and recursive queries using a fluent Rust API.
The WITH clause is supported across different statement types, including Query Select, InsertStatement, UpdateStatement, and DeleteStatement. It facilitates the creation of subqueries that act like virtual tables, which can be reused multiple times within the main query body.
Sources: src/query/with.rs, src/backend/query_builder.rs:26-30, README.md:120-125
Architecture and Components
The CTE implementation in SeaQuery is structured around several key data structures that represent the components of a WITH clause.
Core Data Structures
| Structure | Description |
|---|---|
WithClause | The primary container for a WITH statement, holding recursion flags and a collection of CTE expressions. |
CommonTableExpression | Represents a single named temporary table, including its column definitions, query source, and materialization hints. |
CteQuery | An enum defining the source of a CTE, which can be a SubQuery (Select/Insert/Update/Delete) or a static set of Values. |
WithQuery | A specialized statement builder that combines a WithClause with a primary query to be executed. |
Sources: src/query/with.rs:10-15, src/query/with.rs:65-75, src/backend/query_builder.rs:600-610
Logic Flow for Statement Preparation
The following diagram illustrates how the QueryBuilder processes a WITH clause during the SQL generation phase.
The query builder ensures that the WITH clause is prepended to the main statement (SELECT, INSERT, UPDATE, or DELETE) and correctly handles comma separation between multiple CTE definitions.
Sources: src/backend/query_builder.rs:608-650, src/backend/query_builder.rs:655-680
CTE Configuration and Features
Recursion
SeaQuery supports recursive CTEs via the recursive flag in the WithClause. When enabled, the builder prepends the RECURSIVE keyword. Additionally, advanced recursive options like SEARCH (BREADTH/DEPTH) and CYCLE detection are supported for backends that implement these SQL standards.
Sources: src/query/with.rs:18-25, src/backend/query_builder.rs:615-640
Materialization Hints
Developers can provide hints to the database optimizer regarding whether a CTE should be "materialized" (calculated once and stored) or "not materialized" (inlined into the main query).
MATERIALIZED: Forces the database to compute the result set once.NOT MATERIALIZED: Encourages the database to inline the subquery for potential optimization.
Sources: src/query/with.rs:72-74, src/backend/query_builder.rs:710-720
Values as CTE Source
While most CTEs are derived from subqueries, SeaQuery allows a CTE to be defined using a static set of rows via CteQuery::Values. This is useful for creating temporary reference data within a query without hitting an actual table.
Sources: src/query/with.rs:105-110, src/backend/query_builder.rs:698-705
Implementation Details in Query Builders
The QueryBuilder trait defines the contract for how different database backends should render the WITH clause. The common implementation handles the heavy lifting of iterating through the CommonTableExpression list and formatting names, columns, and subqueries.
Key Backend Functions
| Function | Purpose |
|---|---|
prepare_with_clause | Entry point for writing the entire WITH block. |
prepare_with_clause_start | Writes WITH and optionally RECURSIVE. |
prepare_with_query_clause_common_table | Formats a single CTE entry (Name, Cols, AS, Query). |
prepare_with_query_clause_materialization | Appends materialization hints if specified. |
Sources: src/backend/query_builder.rs:608-615, src/backend/query_builder.rs:655-665, src/backend/query_builder.rs:685-700
Conclusion
Common Table Expressions in SeaQuery provide a robust mechanism for building modular and readable SQL. By abstracting the complexities of recursive syntax and materialization hints, SeaQuery allows for the construction of sophisticated data pipelines directly within the Rust type system, ensuring that temporary result sets are handled consistently across MySQL, PostgreSQL, and SQLite backends.