Instance Method

with(_:columns:as:)

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

@discardableResult func with(_ name: some SQLExpression, columns: [String], 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, usually a SELECT query.

Discussion

Example usage:

try await sqlDatabase.update("table1")
    .with("c", columns: ["a"], as: SQLSubquery.select {$0
        .column("x")
        .from("table3")
    })
    .set("foo", to: "bar")
    .where("foo", .equal, SQLColumn("a", table: "c"))
    .run()

Warning

As with SQLCommonTableExpression, SQLCommonTableExpressionBuilder does NOT validate that a non-recursive CTE’s query is not self-referential. 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.