Instance Method

with(isRecursive:name:columns:as:)

Specify a potentially-recursive common table expression for use elsewhere in a query.

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

Parameters

isRecursive: Specifies whether or not the CTE is recursive.
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. If the CTE is recursive, 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

This is the common “funnel” method invoked by all other methods provided by SQLCommonTableExpressionBuilder. Most users will not need to call this method directly.

See with(_:columns:as:) and with(recursive:columns:as:) for usage examples.

Warning

As with SQLCommonTableExpression, SQLCommonTableExpressionBuilder does NOT validate that a recursive CTE’s query takes the proper form, nor that a non-recursive CTE’s query is not self-referential. It is the responsibility of the user to specify the flag accurately. Failure to do so will result in generating invalid SQL.