Instance Method

with(recursive:columns:as:)

Specify a subquery to include as a recursive common table expression, for use elsewhere in the overall query.

@discardableResult func with(recursive name: some SQLExpression, columns: [any SQLExpression] = [], as query: some SQLExpression) -> Self

Parameters

name: The name to assign to the query’s results.
columns: An optional list of unqualified column names to use for referencing the query’s results. If no column names are provided, the names are inferred from the query. If column names are provided, the number of names provided must match the number of columns returned by the query.
query: An expression which provides the contents of the CTE. For a recursive CTE, this must be an expression representing at least one SELECT statement which does not refer to the CTE and at least one UNION ALL or UNION DISTINCT clause terminating with a SELECT statement which explicitly refers to the CTE itself.

Discussion

Example usage:

try await sqlDatabase.update("table1")
    .with(recursive: "c", columns: ["n"], as: SQLSubquery
        .union { $0.column(SQLBind("1"), as: "n") }
        .union(all: { $0
            .column(SQLBinaryExpression("n", .add, 1))
            .from("c").where("n", .lessThan, 3)
        }).finish())
    .set("foo", to: "bar")
    .where("foo", .equal, SQLColumn("n", table: "c"))
    .run()

Warning

As with SQLCommonTableExpression, SQLCommonTableExpressionBuilder does NOT validate that a recursive CTE’s query takes the proper form. It is the responsibility of the user to invoke the appropriate variant of this method. Failure to do so will result in generating invalid SQL.