CommonTableExpression

The table name of the common table expression.

For example:

// WITH answer AS (SELECT 42) ...
let answer = CommonTableExpression(
    named: "answer",
    sql: "SELECT 42")
answer.tableName // "answer"

Creates a common table expression from a request.

For example:

// WITH p AS (SELECT * FROM player) ...
let p = CommonTableExpression<Void>(
    named: "p",
    request: Player.all())

// WITH p AS (SELECT * FROM player) ...
let p = CommonTableExpression<Void>(
    named: "p",
    request: SQLRequest<Player>(sql: "SELECT * FROM player"))

Creates a common table expression from an SQL string and optional arguments.

For example:

// WITH p AS (SELECT * FROM player WHERE name = 'O''Brien') ...
let p = CommonTableExpression<Void>(
    named: "p",
    sql: "SELECT * FROM player WHERE name = ?",
    arguments: ["O'Brien"])

Creates a common table expression from an SQL literal.

Literals allow you to safely embed raw values in your SQL, without any risk of syntax errors or SQL injection:

// WITH p AS (SELECT * FROM player WHERE name = 'O''Brien') ...
let name = "O'Brien"
let p = CommonTableExpression<Void>(
    named: "p",
    literal: "SELECT * FROM player WHERE name = \(name)")

Creates a request for all rows of the common table expression.

You can fetch from this request:

// WITH answer AS (SELECT 42 AS value)
// SELECT * FROM answer
struct Answer: Decodable, FetchableRecord {
    var value: Int
}
let cte = CommonTableExpression<Answer>(
    named: "answer",
    sql: "SELECT 42 AS value")
let answer = try cte.all().with(cte).fetchOne(db)!
print(answer.value) // prints 42

You can embed this request as a subquery:

// WITH answer AS (SELECT 42 AS value)
// SELECT * FROM player
// WHERE score = (SELECT * FROM answer)
let answer = CommonTableExpression(
    named: "answer",
    sql: "SELECT 42 AS value")
let players = try Player
    .filter(Column("score") == answer.all())
    .with(answer)
    .fetchAll(db)

An SQL expression that checks the inclusion of an expression in a common table expression.

let playerNameCTE = CommonTableExpression(
    named: "playerName",
    request: Player.select(Column("name"))

// name IN playerName
playerNameCTE.contains(Column("name"))

Creates an association to a common table expression that you can join or include in another request.

The key of the returned association is the table name of the common table expression.

Creates an association to a common table expression that you can join or include in another request.

The key of the returned association is the table name of the common table expression.