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 with a full-text predicate added to the eventual set of already applied predicates.

    // SELECT * FROM book WHERE book MATCH '...'
    var request = Book.all()
    request = request.matching(pattern)
    

    If the search pattern is nil, the request does not match any database row.

    Declaration

    Swift

    public func matching(_ pattern: FTS3Pattern?) -> QueryInterfaceRequest<T>
  • A cursor over fetched records.

    let request: QueryInterfaceRequest<Player> = ...
    let players = try request.fetchCursor(db) // Cursor of Player
    while let player = try players.next() {   // Player
        ...
    }
    

    If the database is modified during the cursor iteration, the remaining elements are undefined.

    The cursor must be iterated in a protected dispath queue.

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    public func fetchCursor(_ db: Database) throws -> RecordCursor<RowDecoder>

    Parameters

    db

    A database connection.

    Return Value

    A cursor over fetched records.

  • An array of fetched records.

    let request: QueryInterfaceRequest<Player> = ...
    let players = try request.fetchAll(db) // [Player]
    

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    public func fetchAll(_ db: Database) throws -> [RowDecoder]

    Parameters

    db

    A database connection.

    Return Value

    An array of records.

  • The first fetched record.

    let request: QueryInterfaceRequest<Player> = ...
    let player = try request.fetchOne(db) // Player?
    

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    public func fetchOne(_ db: Database) throws -> RowDecoder?

    Parameters

    db

    A database connection.

    Return Value

    An optional record.

  • Creates a request that prefetches an association.

    Declaration

    Swift

    public func including<A>(all association: A) -> QueryInterfaceRequest where T == A.OriginRowDecoder, A : AssociationToMany
  • Creates a request that includes an association. The columns of the associated record are selected. The returned request does not require that the associated database table contains a matching row.

    Declaration

    Swift

    public func including<A>(optional association: A) -> QueryInterfaceRequest where T == A.OriginRowDecoder, A : Association
  • Creates a request that includes an association. The columns of the associated record are selected. The returned request requires that the associated database table contains a matching row.

    Declaration

    Swift

    public func including<A>(required association: A) -> QueryInterfaceRequest where T == A.OriginRowDecoder, A : Association
  • Creates a request that joins an association. The columns of the associated record are not selected. The returned request does not require that the associated database table contains a matching row.

    Declaration

    Swift

    public func joining<A>(optional association: A) -> QueryInterfaceRequest where T == A.OriginRowDecoder, A : Association
  • Creates a request that joins an association. The columns of the associated record are not selected. The returned request requires that the associated database table contains a matching row.

    Declaration

    Swift

    public func joining<A>(required association: A) -> QueryInterfaceRequest where T == A.OriginRowDecoder, A : Association
  • 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 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 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 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 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 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

  • A cursor over fetched rows.

    let request: QueryInterfaceRequest<Row> = ...
    let rows = try request.fetchCursor(db) // RowCursor
    while let row = try rows.next() {  // Row
        let id: Int64 = row[0]
        let name: String = row[1]
    }
    

    Fetched rows are reused during the cursor iteration: don’t turn a row cursor into an array with Array(rows) or rows.filter { ... } since you would not get the distinct rows you expect. Use Row.fetchAll(...) instead.

    For the same reason, make sure you make a copy whenever you extract a row for later use: row.copy().

    If the database is modified during the cursor iteration, the remaining elements are undefined.

    The cursor must be iterated in a protected dispath queue.

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    public func fetchCursor(_ db: Database) throws -> RowCursor

    Parameters

    db

    A database connection.

    Return Value

    A cursor over fetched rows.

  • An array of fetched rows.

    let request: QueryInterfaceRequest<Row> = ...
    let rows = try request.fetchAll(db)
    

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    public func fetchAll(_ db: Database) throws -> [Row]

    Parameters

    db

    A database connection.

    Return Value

    An array of fetched rows.

  • The first fetched row.

    let request: QueryInterfaceRequest<Row> = ...
    let row = try request.fetchOne(db)
    

    Throws

    A DatabaseError is thrown whenever an SQLite error occurs.

    Declaration

    Swift

    public func fetchOne(_ db: Database) throws -> Row?

    Parameters

    db

    A database connection.

    Return Value

    An optional row.