QueryInterfaceRequest

Returns a request which embeds the common table expression.

If a common table expression with the same table name had already been embedded, it is replaced by the new one.

For example, you can build a request that fetches all chats with their latest message:

let latestMessageRequest = Message
    .annotated(with: max(Column("date")))
    .group(Column("chatID"))

let latestMessageCTE = CommonTableExpression(
    named: "latestMessage",
    request: latestMessageRequest)

let latestMessage = Chat.association(
    to: latestMessageCTE,
    on: { chat, latestMessage in
        chat[Column("id")] == latestMessage[Column("chatID")]
    })

// WITH latestMessage AS
//   (SELECT *, MAX(date) FROM message GROUP BY chatID)
// SELECT chat.*, latestMessage.*
// FROM chat
// LEFT JOIN latestMessage ON chat.id = latestMessage.chatID
let request = Chat.all()
    .with(latestMessageCTE)
    .including(optional: latestMessage)

Undocumented

Creates a request which selects selection promise.

// SELECT id, email FROM player
var request = Player.all()
request = request.select { db in [Column("id"), Column("email")] }

Any previous selection is replaced:

// SELECT email FROM player
request
    .select { db in [Column("id")] }
    .select { db in [Column("email")] }

Creates a request which selects selection, and fetches values of type type.

try dbQueue.read { db in
    // SELECT max(score) FROM player
    let request = Player.all().select([max(Column("score"))], as: Int.self)
    let maxScore: Int? = try request.fetchOne(db)
}

Creates a request which selects selection, and fetches values of type type.

try dbQueue.read { db in
    // SELECT max(score) FROM player
    let request = Player.all().select(max(Column("score")), as: Int.self)
    let maxScore: Int? = try request.fetchOne(db)
}

Creates a request which selects sql, and fetches values of type type.

try dbQueue.read { db in
    // SELECT max(score) FROM player
    let request = Player.all().select(sql: "max(score)", as: Int.self)
    let maxScore: Int? = try request.fetchOne(db)
}

Creates a request which selects an SQL literal, and fetches values of type type.

try dbQueue.read { db in
    // SELECT IFNULL(name, 'Anonymous') FROM player WHERE id = 42
    let request = Player.
        .filter(primaryKey: 42)
        .select(
            SQLLiteral(
                sql: "IFNULL(name, ?)",
                arguments: ["Anonymous"]),
            as: String.self)
    let name: String? = try request.fetchOne(db)
}

With Swift 5, you can safely embed raw values in your SQL queries, without any risk of syntax errors or SQL injection:

try dbQueue.read { db in
    // SELECT IFNULL(name, 'Anonymous') FROM player WHERE id = 42
    let request = Player.
        .filter(primaryKey: 42)
        .select(
            literal: "IFNULL(name, \("Anonymous"))",
            as: String.self)
    let name: String? = try request.fetchOne(db)
}

Creates a request which appends selection promise.

// SELECT id, email, name FROM player
var request = Player.all()
request = request
    .select([Column("id"), Column("email")])
    .annotated(with: { db in [Column("name")] })

Creates a request with the provided predicate promise added to the eventual set of already applied predicates.

// SELECT * FROM player WHERE 1
var request = Player.all()
request = request.filter { db in true }

Creates a request with the provided orderings promise.

// SELECT * FROM player ORDER BY name
var request = Player.all()
request = request.order { _ in [Column("name")] }

Any previous ordering is replaced:

// SELECT * FROM player ORDER BY name
request
    .order{ _ in [Column("email")] }
    .reversed()
    .order{ _ in [Column("name")] }

Creates a request that reverses applied orderings.

// SELECT * FROM player ORDER BY name DESC
var request = Player.all().order(Column("name"))
request = request.reversed()

If no ordering was applied, the returned request is identical.

// SELECT * FROM player
var request = Player.all()
request = request.reversed()

Creates a request without any ordering.

// SELECT * FROM player
var request = Player.all().order(Column("name"))
request = request.unordered()

Creates a request grouped according to expressions promise.

Creates a request with the provided predicate promise added to the eventual set of already applied predicates.

Creates a request that allows you to define expressions that target a specific database table.

In the example below, the “team.avgScore < player.score” condition in the ON clause could be not achieved without table aliases.

struct Player: TableRecord {
    static let team = belongsTo(Team.self)
}

// SELECT player.*, team.*
// JOIN team ON ... AND team.avgScore < player.score
let playerAlias = TableAlias()
let request = Player
    .all()
    .aliased(playerAlias)
    .including(required: Player.team.filter(Column("avgScore") < playerAlias[Column("score")])

Creates a request which returns distinct rows.

// SELECT DISTINCT * FROM player
var request = Player.all()
request = request.distinct()

// SELECT DISTINCT name FROM player
var request = Player.select(Column("name"))
request = request.distinct()

Creates a request which fetches limit rows, starting at offset.

// SELECT * FROM player LIMIT 1
var request = Player.all()
request = request.limit(1)

Any previous limit is replaced.

Creates a request bound to type RowDecoder.

The returned request can fetch if the type RowDecoder is fetchable (Row, value, record).

// Int?
let maxScore = try Player
    .select(max(scoreColumn))
    .asRequest(of: Int.self)    // <--
    .fetchOne(db)

Creates a request which appends aggregates to the current selection.

// SELECT player.*, COUNT(DISTINCT book.id) AS bookCount
// FROM player LEFT JOIN book ...
var request = Player.all()
request = request.annotated(with: Player.books.count)

Creates a request which appends aggregates to the current selection.

// SELECT player.*, COUNT(DISTINCT book.id) AS bookCount
// FROM player LEFT JOIN book ...
var request = Player.all()
request = request.annotated(with: [Player.books.count])

Creates a request which appends the provided aggregate predicate to the eventual set of already applied predicates.

// SELECT player.*
// FROM player LEFT JOIN book ...
// HAVING COUNT(DISTINCT book.id) = 0
var request = Player.all()
request = request.having(Player.books.isEmpty)

Deletes matching rows; returns the number of deleted rows.

Updates matching rows; returns the number of updated rows.

For example:

try dbQueue.write { db in
    // UPDATE player SET score = 0
    try Player.all().updateAll(db, [Column("score").set(to: 0)])
}

Updates matching rows; returns the number of updated rows.

For example:

try dbQueue.write { db in
    // UPDATE player SET score = 0
    try Player.all().updateAll(db, Column("score").set(to: 0))
}