QueryInterfaceRequest

Creates a request which appends aggregates to the current selection.

// SELECT player.*, COUNT(DISTINCT book.rowid) 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.rowid) 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.rowid) = 0
var request = Player.all()
request = request.having(Player.books.isEmpty)

Creates a request which selects selection.

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

Any previous selection is replaced:

// SELECT email FROM player
request
    .select([Column("id")])
    .select([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.

// SELECT id, email, name FROM player
var request = Player.all()
request = request
    .select([Column("id"), Column("email")])
    .annotated(with: [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 added to the eventual set of already applied predicates.

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 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 bound to type Target.

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

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

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") <- 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") <- 0)
}