QueryInterfaceRequest

public struct QueryInterfaceRequest<T>

QueryInterfaceRequest is a request that generates SQL for you.

For example:

try dbQueue.read { db in
    let request = Player
        .filter(Column("score") > 1000)
        .order(Column("name"))
    let players = try request.fetchAll(db) // [Player]
}

See https://github.com/groue/GRDB.swift#the-query-interface

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

    Declaration

    Swift

    public func annotated(with aggregates: AssociationAggregate<RowDecoder>...) -> 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])
    

    Declaration

    Swift

    public func annotated(with aggregates: [AssociationAggregate<RowDecoder>]) -> QueryInterfaceRequest
  • 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)
    

    Declaration

    Swift

    public func having(_ predicate: AssociationAggregate<RowDecoder>) -> QueryInterfaceRequest
  • 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")])
    

    Declaration

    Swift

    public func select(_ selection: [SQLSelectable]) -> QueryInterfaceRequest
  • 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)
    }
    

    Declaration

    Swift

    public func select<RowDecoder>(_ selection: [SQLSelectable], as type: RowDecoder.Type)
        -> QueryInterfaceRequest<RowDecoder>
  • 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)
    }
    

    Declaration

    Swift

    public func select<RowDecoder>(_ selection: SQLSelectable..., as type: RowDecoder.Type)
        -> QueryInterfaceRequest<RowDecoder>
  • 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)
    }
    

    Declaration

    Swift

    public func select<RowDecoder>(
        sql: String,
        arguments: StatementArguments = StatementArguments(),
        as type: RowDecoder.Type)
        -> QueryInterfaceRequest<RowDecoder>
  • 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)
    }
    

    Declaration

    Swift

    public func select<RowDecoder>(
        literal sqlLiteral: SQLLiteral,
        as type: RowDecoder.Type)
        -> QueryInterfaceRequest<RowDecoder>
  • 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")])
    

    Declaration

    Swift

    public func annotated(with selection: [SQLSelectable]) -> QueryInterfaceRequest
  • 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 }
    

    Declaration

    Swift

    public func filter(_ predicate: @escaping (Database) throws -> SQLExpressible) -> QueryInterfaceRequest
  • 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")] }
    

    Declaration

    Swift

    public func order(_ orderings: @escaping (Database) throws -> [SQLOrderingTerm]) -> QueryInterfaceRequest
  • 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()
    

    Declaration

    Swift

    public func reversed() -> QueryInterfaceRequest
  • Creates a request without any ordering.

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

    Declaration

    Swift

    public func unordered() -> QueryInterfaceRequest
  • Creates a request grouped according to expressions promise.

    Declaration

    Swift

    public func group(_ expressions: @escaping (Database) throws -> [SQLExpressible]) -> QueryInterfaceRequest
  • Creates a request with the provided predicate added to the eventual set of already applied predicates.

    Declaration

    Swift

    public func having(_ predicate: SQLExpressible) -> QueryInterfaceRequest
  • 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()
    

    Declaration

    Swift

    public func distinct() -> QueryInterfaceRequest
  • 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.

    Declaration

    Swift

    public func limit(_ limit: Int, offset: Int? = nil) -> QueryInterfaceRequest
  • 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")])
    

    Declaration

    Swift

    public func aliased(_ alias: TableAlias) -> QueryInterfaceRequest
  • 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)
    

    Declaration

    Swift

    public func asRequest<RowDecoder>(of type: RowDecoder.Type) -> QueryInterfaceRequest<RowDecoder>

    Parameters

    type

    The fetched type Target

    Return Value

    A typed request bound to type Target.

  • Deletes matching rows; returns the number of deleted rows.

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    @discardableResult
    public func deleteAll(_ db: Database) throws -> Int

    Parameters

    db

    A database connection.

    Return Value

    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])
    }
    

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    @discardableResult
    public func updateAll(
        _ db: Database,
        onConflict conflictResolution: Database.ConflictResolution? = nil,
        _ assignments: [ColumnAssignment]) throws -> Int

    Parameters

    db

    A database connection.

    conflictResolution

    A policy for conflict resolution, defaulting to the record’s persistenceConflictPolicy.

    assignments

    An array of column assignments.

    Return Value

    The number of updated 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)
    }
    

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    @discardableResult
    public func updateAll(
        _ db: Database,
        onConflict conflictResolution: Database.ConflictResolution? = nil,
        _ assignment: ColumnAssignment,
        _ otherAssignments: ColumnAssignment...)
        throws -> Int

    Parameters

    db

    A database connection.

    conflictResolution

    A policy for conflict resolution, defaulting to the record’s persistenceConflictPolicy.

    assignment

    A column assignment.

    otherAssignments

    Eventual other column assignments.

    Return Value

    The number of updated rows.