Aggregate and Database Functions
Relevant source files
The following files were used as context for generating this wiki page:
Aggregate and database functions in SeaQuery provide a structured way to invoke SQL functions across different database backends like MySQL, Postgres, and SQLite. These functions allow developers to perform calculations on sets of values (aggregates), transform data (casting, mathematical operations), and utilize engine-specific features through a common Rust API.
The system is built around the Func enum and the FunctionCall struct, which manage function names, arguments, and modifiers such as DISTINCT. This architecture ensures that dynamic queries remain type-safe and backend-agnostic until the final SQL string generation.
Sources: README.md:465-470, src/lib.rs:240-245, src/backend/query_builder.rs:655-660
Architecture and Core Components
SeaQuery represents functions as part of its internal Abstract Syntax Tree (AST). The Expr::FunctionCall variant encapsulates a FunctionCall object, which contains the function identifier and its parameters.
Component Relationship
The following diagram illustrates how functions are integrated into the query building process:
The FunctionCall struct maintains a list of arguments and corresponding modifiers (like DISTINCT) to ensure correct SQL syntax during generation.
Sources: src/backend/query_builder.rs:296-300, src/backend/query_builder.rs:666-680
Key Data Structures
| Structure | Description | Source |
|---|---|---|
Func | An enum representing standard SQL functions (e.g., Max, Sum, Count, Cast). | src/backend/query_builder.rs:656-680 |
FunctionCall | A struct holding the Func, arguments (Vec<Expr>), and modifiers. | src/backend/query_builder.rs:666 |
Expr::FunctionCall | The AST node that allows functions to be used within SELECT, WHERE, and HAVING clauses. | src/backend/query_builder.rs:296 |
Standard Aggregate Functions
SeaQuery supports common SQL aggregate functions that operate on groups of rows. These are translated by the QueryBuilder into the appropriate keyword for the target database.
Supported Aggregates
- SUM: Calculates the total sum of a numeric column.
- COUNT: Returns the number of rows or non-null values.
- MAX / MIN: Finds the maximum or minimum value in a set.
- AVG: Calculates the average value.
// Example of an aggregate function usage
let query = Query::select()
.expr(Func::sum(Expr::col((Char::Table, Char::SizeH))))
.from(Char::Table)
.to_owned();
Sources: README.md:470-475, src/backend/query_builder.rs:658-665
Transformation and Utility Functions
Beyond aggregates, SeaQuery provides utility functions for data manipulation and type conversion.
Type Casting
The Func::Cast variant allows for explicit type conversion within a query. This is represented via Func::cast_as(expr, type), which generates the CAST(x AS type) SQL syntax across all supported backends.
Sources: README.md:488-490, src/backend/query_builder.rs:670
String and Math Functions
Common scalar functions are provided to manipulate individual values:
- String:
LOWER,UPPER,CHAR_LENGTH. - Math:
ABS,RANDOM,ROUND,MD5. - Selection:
COALESCE,GREATEST,LEAST,IFNULL.
Sources: src/backend/query_builder.rs:658-675
Custom Functions
For database-specific functions not natively included in the Func enum, SeaQuery provides a "Custom Function" escape hatch. By implementing the Iden trait for a struct, developers can invoke any function name.
Implementation Flow
This allows for complete flexibility when working with extensions or proprietary database features.
Sources: README.md:507-515, src/backend/query_builder.rs:656-657
Backend Translation Logic
The QueryBuilder trait defines how these abstract function calls are turned into raw SQL. While many functions like SUM and COUNT are universal, others require backend-specific naming.
| Abstract Func | MySQL Translation | Postgres Translation | SQLite Translation |
|---|---|---|---|
IfNull | IFNULL | COALESCE | IFNULL |
Random | RAND() | RANDOM() | RANDOM() |
CharLength | CHAR_LENGTH | LENGTH | LENGTH |
Translation is handled in the prepare_function_name and prepare_function_arguments methods of the respective QueryBuilder implementations.
Sources: src/backend/query_builder.rs:655-675, src/backend/query_builder.rs:980-1000
Summary
Aggregate and Database Functions in SeaQuery provide a robust, type-safe abstraction for SQL function execution. By utilizing the Func enum and FunctionCall structures, the library maintains backend portability while allowing for complex data operations. The inclusion of Custom function support ensures that the builder can scale to handle any database-specific requirement, making it a versatile tool for dynamic query construction.
Sources: src/lib.rs:240-250, src/backend/query_builder.rs:296-300