QueryBuilder

public final class QueryBuilder<Database, Result> where Database : QuerySupporting

Helper for constructing and executing Database.Querys.

Query builder has methods like all(), first(), and chunk(max:closure:) for fetching data. Use the filter(...) methods combined with operators like == and >= to filter the result set.

let users = try User.query(on: req).filter(\.name == "Vapor").all()

Use the query(on:) on Model to create a QueryBuilder for a model.

  • The DatabaseQuery being built.

    Declaration

    Swift

    public var query: Database.Query
  • The connection this query will be excuted on.

    Warning

    Avoid using the connection manually.

    Declaration

    Swift

    public let connection: Future<Database.Connection>
  • Joins another model to this query builder. You can filter your existing query by joined models.

    let users = try User.query(on: conn)
        .join(\Pet.userID, to: \User.id)
        .filter(\Pet.type == .cat)
        .all()
    print(users) // Future<[User]>
    

    You can also decode joined models from the result set.

    let usersAndPets = try User.query(on: conn)
        .join(\Pet.userID, to: \User.id)
        .filter(\Pet.type == .cat)
        .alsoDecode(Pet.self)
        .all()
    print(usersAndPets) // Future<[(User, Pet)]>
    

    Declaration

    Swift

    public func join<A, B, C, D>(_ joinedKey: KeyPath<A, B>, to baseKey: KeyPath<C, D>, method: Database.QueryJoinMethod = Database.queryJoinMethodDefault) -> Self

    Parameters

    joinedKey

    Key from new model to join to this query.

    baseKey

    Field on existing model to use while joining. The value in this field should match values from the other model’s field. This can be a key from the query builder’s type, or a previously joined model.

    method

    Join method to use. Uses the database’s default join method if none is supplied.

    Return Value

    Self for chaining.

  • Returns the sum of all entries for the supplied field.

    let totalLikes = try Post.query(on: conn).sum(\.likes)
    

    If a default value is supplied, it will be used when the sum’s result set is empty and no sum can be determined.

    let totalViralPostLikes = try Post.query(on: conn)
        .filter(\.likes >= 10_000_000)
        .sum(\.likes, default: 0)
    

    Declaration

    Swift

    public func sum<T>(_ field: KeyPath<Result, T>, default: T? = nil) -> Future<T> where T: Decodable

    Parameters

    field

    Field to sum.

    default

    Optional default to use.

    Return Value

    A Future containing the sum.

  • Returns the average of all entries for the supplied field.

    let averageLikes = try Post.query(on: conn).average(\.likes)
    

    Declaration

    Swift

    public func average<T>(_ field: KeyPath<Result, T>) -> Future<T> where T : Decodable

    Parameters

    field

    Field to average.

    Return Value

    A Future containing the average.

  • Returns the minimum value of all entries for the supplied field.

    let leastLikes = try Post.query(on: conn).min(\.likes)
    

    Declaration

    Swift

    public func min<T>(_ field: KeyPath<Result, T>) -> Future<T> where T : Decodable

    Parameters

    field

    Field to find min for.

    Return Value

    A Future containing the min.

  • Returns the maximum value of all entries for the supplied field.

    let mostLikes = try Post.query(on: conn).max(\.likes)
    

    Declaration

    Swift

    public func max<T>(_ field: KeyPath<Result, T>) -> Future<T> where T : Decodable

    Parameters

    field

    Field to find max for.

    Return Value

    A Future containing the max.

  • Perform an aggregate action on the supplied field. Normally you will use one of the convenience methods like min(...) or count(...) instead.

    let mostLikes = try Post.query(on: conn).aggregate(.max, field: \.likes, as: Int.self)
    

    Declaration

    Swift

    public func aggregate<D, T>(_ method: Database.QueryAggregate, field: KeyPath<Result, T>, as type: D.Type = D.self) -> Future<D>
        where D: Decodable

    Parameters

    method

    Aggregate method to use.

    field

    Field to find max for.

    type

    Decodable type to decode the aggregate value as.

    Return Value

    A Future containing the aggregate.

  • Returns the number of results for this query.

    let numPosts = try Post.query(on: conn).count()
    

    Declaration

    Swift

    public func count() -> Future<Int>

    Return Value

    A Future containing the count.

  • Performs an create action on the database with the supplied data.

    // creates a new User with custom data.
    User.query(on: conn).create(data: ["name": "Vapor"])
    

    Warning

    This method will not invoke model lifecycle hooks.

    Declaration

    Swift

    public func create<E>(data: E) -> Future<Void> where E : Encodable

    Parameters

    data

    Encodable data to create.

    Return Value

    A Future that will be completed when the create is done.

  • Performs an update action on the database with the supplied data.

    // set all users' names to "Vapor"
    User.query(on: conn).update(data: ["name": "Vapor"])
    

    Warning

    This method will not invoke model lifecycle hooks.

    Declaration

    Swift

    public func update<E>(data: E) -> Future<Void> where E : Encodable

    Parameters

    data

    Encodable data to update.

    Return Value

    A Future that will be completed when the update is done.

  • Sets a single key-value pair to be updated when the query is run.

    Planet.query(on: conn).update(\.name, to: "Earth").update(\.galaxyID, to: 5).run()
    

    Declaration

    Swift

    public func update<T>(_ field: KeyPath<Result, T>, to value: T) -> Self where T : Encodable

    Parameters

    field

    KeyPath of field to update.

    value

    Encodable value to update field to.

    Return Value

    Self for chaining.

  • Deletes all entities that would be fetched by this query.

    try User.query(on: conn).filter(\.name == "foo").delete()
    

    Declaration

    Swift

    public func delete(force: Bool = false) -> Future<Void>

    Return Value

    A Future that will be completed when the delete is done.

  • Restores all soft-deleted entities that would be fetched by this query.

    try User.query(on: conn, withSoftDeleted: true).filter(\.name == "foo").restore()
    

    Declaration

    Swift

    public func restore() -> Future<Void>

    Return Value

    A Future that will be completed when the delete is done.

  • Adds an additional type D to be decoded when run. The new result for this query will be a tuple containing the previous result and this new result.

    let joined = try User.query(on: req)
        .join(\Pet.userID, to: \User.id)
        .alsoDecode(Pet.self)
        .all()
    print(joined) // Future<[(User, Pet)]>
    

    Declaration

    Swift

    public func alsoDecode<M>(_ type: M.Type) -> QueryBuilder<Database, (Result, M)> where M : Model

    Parameters

    type

    New model type D to also decode.

    Return Value

    QueryBuilder decoding type (Result, D).

  • Adds an additional type D to be decoded when run. The new result for this query will be a tuple containing the previous result and this new result.

    let joined = try User.query(on: req)
        .join(\Pet.userID, to: \User.id)
        .alsoDecode(PetDetail.self, "pets")
        .all()
    print(joined) // Future<[(User, PetDetail)]>
    

    Declaration

    Swift

    public func alsoDecode<D>(_ type: D.Type, _ entity: String) -> QueryBuilder<Database, (Result, D)> where D : Decodable

    Parameters

    type

    New decodable type D to also decode.

    entity

    Entity name of this decodable type.

    Return Value

    QueryBuilder decoding type (Result, D).

  • Sets the query to decode Model type D when run. The Model‘s entity will be used.

    let joined = try User.query(on: req)
        .join(Pet.self, field: \.userID, to: \.id)
        .decode(Pet.self)
        .all()
    print(joined) // Future<[Pet]>
    

    Declaration

    Swift

    public func decode<Model>(_ type: Model.Type) -> QueryBuilder<Database, Model> where Model : Model

    Parameters

    type

    New decodable type D to decode.

    Return Value

    QueryBuilder decoding type D.

  • Sets the query to decode Decodable type D when run. The data will be decoded from the supplied entity.

    let joined = try User.query(on: req)
        .join(Pet.self, field: \.userID, to: \.id)
        .decode(data: Pet.self, "pets")
        .all()
    print(joined) // Future<[Pet]>
    

    Declaration

    Swift

    public func decode<D>(data type: D.Type, _ entity: String) -> QueryBuilder<Database, D> where D : Decodable

    Parameters

    type

    New decodable type D to decode.

    entity

    Table or collection to decode from.

    Return Value

    QueryBuilder decoding type D.

  • Sets the query to decode raw output from the database when run.

    let raw = try User.query(on: req).decodeRaw().all()
    print(raw) // Future<[MySQLColumn: MySQLData]>
    

    Declaration

    Swift

    public func decodeRaw() -> QueryBuilder<Database, Database.Output>
  • Sets the query to decode Decodable type D when run. This data type will be decoded from the current Model’s entity.

    let joined = try User.query(on: req)
        .join(Pet.self, field: \.userID, to: \.id)
        .decode(data: Pet.self)
        .all()
    print(joined) // Future<[Pet]>
    

    Declaration

    Swift

    public func decode<D>(data type: D.Type) -> QueryBuilder<Database, D> where D : Decodable

    Parameters

    type

    New decodable type D to decode.

    Return Value

    QueryBuilder decoding type D.

  • Applies a filter to this query. Usually you will use the filter operators to do this.

    let users = try User.query(on: conn)
        .filter(\.name, .equal, "Vapor")
        .all()
    

    Declaration

    Swift

    @discardableResult
    public func filter<T>(_ key: KeyPath<Result, T>, _ method: Database.QueryFilterMethod, _ value: T) -> Self
        where T: Encodable

    Parameters

    key

    Swift KeyPath to a field on the model to filter.

    method

    Query filter method type to use.

    value

    Value to filter by.

    Return Value

    Query builder for chaining.

  • Applies a filter to this query for a joined entity. Usually you will use the filter operators to do this.

    let users = try User.query(on: conn)
        .join(Pet.self, ...)
        .filter(\Pet.type, .equal, .cat)
        .all()
    

    Declaration

    Swift

    @discardableResult
    public func filter<A, T>(_ key: KeyPath<A, T>, _ method: Database.QueryFilterMethod, _ value: T) -> Self
        where T: Encodable

    Parameters

    key

    Swift KeyPath to a field on the model to filter.

    method

    Query filter method type to use.

    value

    Value to filter by.

    Return Value

    Query builder for chaining.

  • Applies a filter to this query using a custom field. Usually you will use the filter operators to do this.

    let users = try User.query(on: conn)
        .filter("name", .equal, "Vapor")
        .all()
    

    Declaration

    Swift

    @discardableResult
    public func filter<T>(_ field: Database.QueryField, _ method: Database.QueryFilterMethod, _ value: T) -> Self
        where T: Encodable

    Parameters

    field

    Name to a field on the model to filter.

    method

    Query filter method type to use.

    value

    Value to filter by.

    Return Value

    Query builder for chaining.

  • Add a manually created filter to the query builder.

    builder.filter(custom: ...)
    

    Declaration

    Swift

    @discardableResult
    public func filter(custom filter: Database.QueryFilter) -> Self

    Return Value

    Query builder for chaining.

  • Creates a sub group for this query. This is useful for grouping multiple filters by .or instead of .and.

    let users = try User.query(on: conn).group(.or) { or in
        or.filter(\.age < 18)
        or.filter(\.age > 65)
    }
    

    Declaration

    Swift

    @discardableResult
    public func group(_ relation: Database.QueryFilterRelation, closure: @escaping (QueryBuilder<Database, Result>) throws -> ()) rethrows -> Self

    Parameters

    relation

    .and or .or relation for the filters added in the closure.

    closure

    A sub-query builder to use for adding grouped filters.

    Return Value

    Query builder for chaining.

  • Saves the supplied model. Calls create(...) if the ID is nil, and update(...) if it exists. If you need to create a model with a pre-existing ID, call create(...) instead.

    let user = User(...)
    User.query(on: conn).save(user)
    

    Declaration

    Swift

    public func save(_ model: Result) -> Future<Result>

    Parameters

    model

    Model to save.

    Return Value

    A Future containing the saved Model.

  • Saves this model as a new item in the database. This method can auto-generate an ID depending on ID type.

    let user = User(...)
    User.query(on: conn).create(user)
    

    Declaration

    Swift

    public func create(_ model: Result) -> Future<Result>

    Parameters

    model

    Model to create.

    Return Value

    A Future containing the created Model.

  • Updates the model. This requires that the model has its ID set.

    let user: User = ...
    User.query(on: conn).update(user, originalID: 42)
    

    Declaration

    Swift

    public func update(_ model: Result, originalID: Result.ID? = nil) -> Future<Result>

    Parameters

    model

    Model to update.

    originalID

    Specify the original ID if the ID has changed.

    Return Value

    A Future containing the created Model.

  • Applies a filter from one of the filter operators (==, !=, etc).

    let users = try User.query(on: conn).filter(\.name == "Vapor").all()
    

    Note

    This method is generic, allowing you to omit type names when filtering using key paths.

    Declaration

    Swift

    @discardableResult
    public func filter(_ value: FilterOperator<Database, Result>) -> Self
  • Applies a filter from one of the filter operators (==, !=, etc) to a joined model.

    let usersWithCats = try User.query(on: conn)
        .join(\Pet.userID, to: \User.id)
        .filter(\Pet.type == .cat)
        .all()
    

    Note

    This method is generic, allowing you to omit type names when filtering using key paths.

    Declaration

    Swift

    @discardableResult
    public func filter<A>(_ value: FilterOperator<Database, A>) -> Self
  • Limits the results of this query to the specified range.

    query.range(2..<5) // returns at most 3 results, offset by 2
    

    Declaration

    Swift

    public func range(_ range: Range<Int>) -> Self

    Return Value

    Query builder for chaining.

  • Limits the results of this query to the specified range.

    query.range(...5) // returns at most 6 results
    

    Declaration

    Swift

    public func range(_ range: PartialRangeThrough<Int>) -> Self

    Return Value

    Query builder for chaining.

  • Limits the results of this query to the specified range.

    query.range(..<5) // returns at most 5 results
    

    Declaration

    Swift

    public func range(_ range: PartialRangeUpTo<Int>) -> Self

    Return Value

    Query builder for chaining.

  • Limits the results of this query to the specified range.

    query.range(5...) // offsets the result by 5
    

    Declaration

    Swift

    public func range(_ range: PartialRangeFrom<Int>) -> Self

    Return Value

    Query builder for chaining.

  • Limits the results of this query to the specified range.

    query.range(2..<5) // returns at most 3 results, offset by 2
    

    Declaration

    Swift

    public func range(_ range: ClosedRange<Int>) -> Self

    Return Value

    Query builder for chaining.

  • Limits the results of this query to the specified range.

    Declaration

    Swift

    public func range(lower: Int = 0, upper: Int? = nil) -> Self

    Parameters

    lower

    Amount to offset the query by.

    upper

    upper - lower = maximum results.

    Return Value

    Query builder for chaining.

  • Runs the query, collecting all of the results into an array.

    let users = User.query(on: conn).all()
    

    Declaration

    Swift

    public func all() -> Future<[Result]>

    Return Value

    A Future containing the results.

  • Returns the first result of the query or nil if no results were returned.

    let users = User.query(on: conn).first()
    

    Declaration

    Swift

    public func first() -> Future<Result?>

    Return Value

    A Future containing the first result, if one exists.

  • Convenience for chunking model results.

    try User.query(on: conn).chunk(max: 32) { chunk in
        // handle chunk of 32 or less users
    }
    

    Declaration

    Swift

    public func chunk(max: Int, closure: @escaping ([Result]) throws -> ()) -> Future<Void>

    Parameters

    max

    Maximum number of entities to include in a single chunk. Actual number in chunk may be less than this number if the result set is not evenly divisible by the supplied number. Note that 0 size chunks may also be supplied.

    closure

    Handles chunks as they are received.

    Return Value

    A Future that will be completed when all chunks have been handled.

  • Runs the `QueryBuilder’s query, decoding results into the handler.

    User.query(on: req).run { user in
        print(user)
    }
    

    Declaration

    Swift

    public func run(_ action: Database.QueryAction? = nil, into handler: @escaping (Result) throws -> () = { _ in }) -> Future<Void>

    Parameters

    handler

    Optional closure to handle results.

    Return Value

    A Future that will be completed when the query has finished.

  • Add a sort to the query builder for a field.

    let users = try User.query(on: conn).sort(\.name, .ascending)
    

    Declaration

    Swift

    public func sort<T>(_ key: KeyPath<Result, T>, _ direction: Database.QuerySortDirection = Database.querySortDirectionAscending) -> Self

    Parameters

    key

    Swift KeyPath to field on model to sort.

    direction

    Direction to sort the fields, ascending or descending.

    Return Value

    Query builder for chaining.

  • Adds a custom sort to the query builder.

    Declaration

    Swift

    public func sort(_ sort: Database.QuerySort) -> Self

    Parameters

    sort

    Custom sort to add.

    Return Value

    Query builder for chaining.