CommonTableExpression

public struct CommonTableExpression<RowDecoder>

A common table expression that can be used with the GRDB query interface.

Show on GitHub

  • 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"

    Declaration

    Swift

    public var tableName: String
    Show on GitHub

  • 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"))

    Declaration

    Swift

    public init<Request: SQLSubqueryable>(
    recursive: Bool = false,
    named tableName: String,
    columns: [String]? = nil,
    request: Request)

    Parameters

    recursive

    Whether this common table expression needs a WITH RECURSIVE sql clause.
    tableName

    The table name of the common table expression.
    columns

    The columns of the common table expression. If nil, the columns are the columns of the request.
    request

    A request.
    Show on GitHub

  • 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"])

    Declaration

    Swift

    public init(
    recursive: Bool = false,
    named tableName: String,
    columns: [String]? = nil,
    sql: String,
    arguments: StatementArguments = StatementArguments())

    Parameters

    recursive

    Whether this common table expression needs a WITH RECURSIVE sql clause.
    tableName

    The table name of the common table expression.
    columns

    The columns of the common table expression. If nil, the columns are the columns of the request.
    sql

    An SQL query.
    arguments

    Statement arguments.
    Show on GitHub

  • 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)")

    Declaration

    Swift

    public init(
    recursive: Bool = false,
    named tableName: String,
    columns: [String]? = nil,
    literal sqlLiteral: SQL)

    Parameters

    recursive

    Whether this common table expression needs a WITH RECURSIVE sql clause.
    tableName

    The table name of the common table expression.
    columns

    The columns of the common table expression. If nil, the columns are the columns of the request.
    sqlLiteral

    An SQL literal.
    Show on GitHub

  • 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)

    Declaration

    Swift

    public func all() -> QueryInterfaceRequest<RowDecoder>
    Show on GitHub

  • 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"))

    Declaration

    Swift

    public func contains(_ element: SQLExpressible) -> SQLExpression
    Show on GitHub

  • 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.

    Declaration

    Swift

    public func association<Destination>(
    to cte: CommonTableExpression<Destination>,
    on condition: @escaping (_ left: TableAlias, _ right: TableAlias) -> SQLExpressible)
-> JoinAssociation<RowDecoder, Destination>

    Parameters

    cte

    A common table expression.
    condition

    A function that returns the joining clause.
    left

    A TableAlias for the left table.
    right

    A TableAlias for the right table.

    Return Value

    An association to the common table expression.

    Show on GitHub

  • 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.

    Declaration

    Swift

    public func association<Destination>(
    to cte: CommonTableExpression<Destination>)
-> JoinAssociation<RowDecoder, Destination>

    Parameters

    cte

    A common table expression.

    Return Value

    An association to the common table expression.

    Show on GitHub

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

    The key of the returned association is the table name of Destination.

    Declaration

    Swift

    public func association<Destination>(
    to destination: Destination.Type,
    on condition: @escaping (_ left: TableAlias, _ right: TableAlias) -> SQLExpressible)
-> JoinAssociation<RowDecoder, Destination>
where Destination: TableRecord

    Parameters

    destination

    The record type at the other side of the association.
    condition

    A function that returns the joining clause.
    left

    A TableAlias for the left table.
    right

    A TableAlias for the right table.

    Return Value

    An association to the common table expression.

    Show on GitHub

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

    The key of the returned association is the table name of Destination.

    Declaration

    Swift

    public func association<Destination>(
    to destination: Destination.Type)
-> JoinAssociation<RowDecoder, Destination>
where Destination: TableRecord

    Parameters

    destination

    The record type at the other side of the association.

    Return Value

    An association to the common table expression.

    Show on GitHub

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

    The key of the returned association is the table name of Destination.

    Declaration

    Swift

    public func association<Destination>(
    to destination: Table<Destination>,
    on condition: @escaping (_ left: TableAlias, _ right: TableAlias) -> SQLExpressible)
-> JoinAssociation<RowDecoder, Destination>

    Parameters

    destination

    The table at the other side of the association.
    condition

    A function that returns the joining clause.
    left

    A TableAlias for the left table.
    right

    A TableAlias for the right table.

    Return Value

    An association to the common table expression.

    Show on GitHub

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

    The key of the returned association is the table name of Destination.

    Declaration

    Swift

    public func association<Destination>(
    to destination: Table<Destination>)
-> JoinAssociation<RowDecoder, Destination>

    Parameters

    destination

    The table at the other side of the association.

    Return Value

    An association to the common table expression.

    Show on GitHub

Available where RowDecoder == Row

  • Creates a common table expression from a request.

    For example:

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

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

    Declaration

    Swift

    public init<Request: SQLSubqueryable>(
    recursive: Bool = false,
    named tableName: String,
    columns: [String]? = nil,
    request: Request)

    Parameters

    recursive

    Whether this common table expression needs a WITH RECURSIVE sql clause.
    tableName

    The table name of the common table expression.
    columns

    The columns of the common table expression. If nil, the columns are the columns of the request.
    request

    A request.
    Show on GitHub

  • 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(
    named: "p",
    sql: "SELECT * FROM player WHERE name = ?",
    arguments: ["O'Brien"])

    Declaration

    Swift

    public init(
    recursive: Bool = false,
    named tableName: String,
    columns: [String]? = nil,
    sql: String,
    arguments: StatementArguments = StatementArguments())

    Parameters

    recursive

    Whether this common table expression needs a WITH RECURSIVE sql clause.
    tableName

    The table name of the common table expression.
    columns

    The columns of the common table expression. If nil, the columns are the columns of the request.
    sql

    An SQL query.
    arguments

    Statement arguments.
    Show on GitHub

  • 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(
    named: "p",
    literal: "SELECT * FROM player WHERE name = \(name)")

    Declaration

    Swift

    public init(
    recursive: Bool = false,
    named tableName: String,
    columns: [String]? = nil,
    literal sqlLiteral: SQL)

    Parameters

    recursive

    Whether this common table expression needs a WITH RECURSIVE sql clause.
    tableName

    The table name of the common table expression.
    columns

    The columns of the common table expression. If nil, the columns are the columns of the request.
    sqlLiteral

    An SQL literal.
    Show on GitHub