Select Statement

Relevant source files

The following files were used as context for generating this wiki page:

• src/lib.rs
• src/backend/query_builder.rs
• src/backend/mysql/query.rs
• src/backend/postgres/query.rs
• tests/mysql/query.rs

The SelectStatement is a core component of the SeaQuery library, providing a dynamic and fluent API for constructing SQL SELECT queries in Rust. It allows developers to build complex abstract syntax trees (AST) representing queries that can be serialized into database-specific SQL for MySQL, PostgreSQL, and SQLite.

The statement builder handles various SQL clauses including projections, joins, filtering, grouping, sorting, and pagination. By abstracting the SQL syntax, it ensures cross-database compatibility and provides safe parameter binding to prevent SQL injection.

Sources: src/lib.rs:20-35, src/backend/query_builder.rs:118-185

Architecture and Core Logic

The architecture of a SelectStatement involves a central data structure that stores the state of the query (columns, tables, conditions) and a translation layer (Query Builders) that converts this state into SQL strings.

Statement Assembly

The conversion from a SelectStatement to SQL is orchestrated by the prepare_select_statement method in the QueryBuilder trait. This method processes the internal components of the statement in a specific sequence to maintain valid SQL syntax.

Order	Clause	Description
1	WITH	Common Table Expressions (CTEs).
2	SELECT	Projections and distinct modifiers.
3	INTO	Target table for selection results (backend specific).
4	FROM	Source tables and index hints.
5	JOIN	Lateral and standard join expressions.
6	WHERE	Filtering conditions.
7	GROUP BY	Aggregate groupings.
8	HAVING	Filtering for grouped results.
9	UNION	Set operations between queries.
10	ORDER BY	Result sorting.
11	LIMIT/OFFSET	Result set pagination.

Sources: src/backend/query_builder.rs:118-185, src/backend/mysql/query.rs:7-60

Key Components

Expressions and Projections

Projections are handled via SelectExpr, which includes the expression itself and optional aliases or windowing functions. Developers can project raw columns, constants, or complex functions like MAX(), MIN(), and COUNT().

// Example: Selecting a column with an alias and a function
Query::select()
    .column(Char::Character)
    .expr(Expr::col(Char::Character).max().alias("max_char"))
    .from(Char::Table);

Sources: src/lib.rs:550-565, src/backend/query_builder.rs:356-373

Joins and Table References

Joins are represented by JoinExpr, supporting various types such as LEFT JOIN, INNER JOIN, and CROSS JOIN. PostgreSQL specifically supports LATERAL joins. Table references can be simple identifiers, subqueries, or values lists.

Filtering and Conditions

Conditions are constructed using Expr or Cond. The SelectStatement provides and_where and cond_where for the WHERE clause, and corresponding methods for the HAVING clause. SeaQuery respects operator precedence, avoiding unnecessary parentheses in the generated SQL.

Sources: src/lib.rs:150-180, src/backend/query_builder.rs:712-730

Backend Specifics

MySQL Implementation

The MysqlQueryBuilder includes specific logic for index hints (USE INDEX, IGNORE INDEX, FORCE INDEX) and LOCK phrases like LOCK IN SHARE MODE. It also supports DISTINCTROW.

Sources: src/backend/mysql/query.rs:21-60, src/backend/mysql/query.rs:240-245

PostgreSQL Implementation

The PostgresQueryBuilder supports advanced features like DISTINCT ON, TABLESAMPLE for probabilistic row selection, and custom PostgreSQL operators (e.g., ILIKE, @@ for text search). It uses $ numbered placeholders for parameters.

Sources: src/backend/postgres/query.rs:43-50, src/backend/postgres/query.rs:90-112, src/backend/postgres/query.rs:250-267

Usage Examples

Dynamic Query Building

One of the primary strengths of the SelectStatement is its ability to apply conditions conditionally using the apply_if method.

fn get_query(id: Option<i32>) -> SelectStatement {
    Query::select()
        .column(Glyph::Image)
        .from(Glyph::Table)
        .apply_if(id, |query, val| {
            query.and_where(Expr::col(Glyph::Id).eq(val));
        })
        .take()
}

Sources: src/lib.rs:128-148, tests/mysql/query.rs:10-50

Conclusion

The SelectStatement module serves as a robust tool for programmatic SQL generation. By leveraging a structured AST and specialized backend builders, it maintains a balance between developer ergonomics in Rust and the diverse requirements of various SQL dialects.

Sources: src/lib.rs:15-40, src/backend/query_builder.rs:118-125