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]
}
-
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
-
Declaration
Swift
public typealias RowDecoder = T
-
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)
orrows.filter { ... }
since you would not get the distinct rows you expect. UseRow.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.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.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.Parameters
db
A database connection.
Return Value
An optional row.