Struct sea_query::query::WithClause

pub struct WithClause { /* private fields */ }

A WITH clause can contain one or multiple common table expressions (CommonTableExpression).

You can use this to generate WithQuery by calling WithClause::query.

These named queries can act as a “query local table” that are materialized during execution and then can be used by the query prefixed with the WITH clause.

A WITH clause can contain multiple of these CommonTableExpression. (Except in the case of recursive WITH query which can only contain one CommonTableExpression).

A CommonTableExpression is a name, column names and a query returning data for those columns.

Some databases (like sqlite) restrict the acceptable kinds of queries inside of the WITH clause common table expressions. These databases only allow SelectStatements to form a common table expression.

Other databases like postgres allow modification queries (UPDATE, DELETE) inside of the WITH clause but they have to return a table. (They must have a RETURNING clause).

sea-query doesn’t check this or restrict the kind of CommonTableExpression that you can create in rust. This means that you can put an UPDATE or DELETE queries into WITH clause and sea-query will succeed in generating that kind of sql query but the execution inside the database will fail because they are invalid.

It is your responsibility to ensure that the kind of WITH clause that you put together makes sense and valid for that database that you are using.

NOTE that for recursive WITH queries (in sql: “WITH RECURSIVE”) you can only have a single CommonTableExpression inside of the WITH clause. That query must match certain requirements:

• It is a query of UNION or UNION ALL of two queries.
• The first part of the query (the left side of the UNION) must be executable first in itself. It must be non-recursive. (Cannot contain self reference)
• The self reference must appear in the right hand side of the UNION.
• The query can only have a single self-reference.
• Recursive data-modifying statements are not supported, but you can use the results of a recursive SELECT query in a data-modifying statement. (like so: WITH RECURSIVE cte_name(a,b,c,d) AS (SELECT … UNION SELECT … FROM … JOIN cte_name ON … WHERE …) DELETE FROM table WHERE table.a = cte_name.a)

It is mandatory to set the Self::cte. With queries must have at least one CTE. Recursive with query generation will panic if you specify more than one CTE.

Examples

use sea_query::{*, IntoCondition, IntoIden, tests_cfg::*};

let base_query = SelectStatement::new()
                    .column(Alias::new("id"))
                    .expr(Expr::val(1i32))
                    .column(Alias::new("next"))
                    .column(Alias::new("value"))
                    .from(Alias::new("table"))
                    .to_owned();

let cte_referencing = SelectStatement::new()
                            .column(Alias::new("id"))
                            .expr(Expr::col(Alias::new("depth")).add(1i32))
                            .column(Alias::new("next"))
                            .column(Alias::new("value"))
                            .from(Alias::new("table"))
                            .join(
                                JoinType::InnerJoin,
                                Alias::new("cte_traversal"),
                                Expr::tbl(Alias::new("cte_traversal"), Alias::new("next")).equals(Alias::new("table"), Alias::new("id")).into_condition()
                            )
                            .to_owned();

let common_table_expression = CommonTableExpression::new()
            .query(
                base_query.clone().union(UnionType::All, cte_referencing).to_owned()
            )
            .column(Alias::new("id"))
            .column(Alias::new("depth"))
            .column(Alias::new("next"))
            .column(Alias::new("value"))
            .table_name(Alias::new("cte_traversal"))
            .to_owned();

let select = SelectStatement::new()
        .column(ColumnRef::Asterisk)
        .from(Alias::new("cte_traversal"))
        .to_owned();

let with_clause = WithClause::new()
        .recursive(true)
        .cte(common_table_expression)
        .cycle(Cycle::new_from_expr_set_using(SimpleExpr::Column(ColumnRef::Column(Alias::new("id").into_iden())), Alias::new("looped"), Alias::new("traversal_path")))
        .to_owned();

let query = select.with(with_clause).to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"WITH RECURSIVE `cte_traversal` (`id`, `depth`, `next`, `value`) AS (SELECT `id`, 1, `next`, `value` FROM `table` UNION ALL SELECT `id`, `depth` + 1, `next`, `value` FROM `table` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `table`.`id`) SELECT * FROM `cte_traversal`"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "table" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "table" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "table"."id") CYCLE "id" SET "looped" USING "traversal_path" SELECT * FROM "cte_traversal""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "table" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "table" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "table"."id") SELECT * FROM "cte_traversal""#
);

Implementations

impl WithClause

pub fn new() -> Self

Constructs a new WithClause.

pub fn recursive(&mut self, recursive: bool) -> &mut Self

Sets whether this clause is a recursive with clause of not. If set to true it will generate a ‘WITH RECURSIVE’ query.

You can only specify a single CommonTableExpression containing a union query if this is set to true.

pub fn search(&mut self, search: Search) -> &mut Self

For recursive WITH queries you can specify the Search clause.

This setting is not meaningful if the query is not recursive.

Some databases don’t support this clause. In that case this option will be silently ignored.

pub fn cycle(&mut self, cycle: Cycle) -> &mut Self

For recursive WITH queries you can specify the Cycle clause.

This setting is not meaningful if the query is not recursive.

Some databases don’t support this clause. In that case this option will be silently ignored.

pub fn cte(&mut self, cte: CommonTableExpression) -> &mut Self

Add a CommonTableExpression to this with clause.

pub fn query<T>(self, query: T) -> WithQuery where
    T: QueryStatementBuilder + 'static, 

You can turn this into a WithQuery using this function. The resulting WITH query will execute the argument query with this WITH clause.

Trait Implementations

impl Clone for WithClause

fn clone(&self) -> WithClause

Returns a copy of the value.

fn clone_from(&mut self, source: &Self)

Performs copy-assignment from source.

impl Debug for WithClause

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter.

impl Default for WithClause

fn default() -> WithClause

Returns the “default value” for a type.