Introduction to Querying with Fluent in Vapor 3

1123 Views

This tutorial is an introduction for how to perform simple filter/sort queries on a database using Vapor's ORM, Fluent.

The following table is a visual representation of the data I will be working with:



I have a Post class, with attributes of:

  • id
  • content
  • created_at, updated_at
  • keyword
  • viewCount

Querying

Let's start with the simplest use case. To query all objects of your model, you can write:

let allObjects = Post.query(on: request).all()

We simply call our query, and pass off our request instance to the newly created thread. Then we call .all() to retrieve all objects for that class.

One of the most common use cases for retrieving an object is finding it by it's object Id. For example, to get the post with an id of 5, we can write the following route:

    func getById(_ request: Request) throws -> Future {
        let objectId = 5
        return Post.find(objectId, on: request).map(to: Post.self) { post in
            guard let post = post else { throw Abort.init(HTTPStatus.notFound) }
            return post
        }
    }


Filter

Oftentimes we only want to fetch specific posts from the database that meet certain criteria. To do so, fluent offers us the filter method. What if, for example, we want to fetch the only posts with a view count of greater than 500? Such a method may look like this:

    func getByViewCount(_ request: Request) throws -> Future<[Post]> {
        return Post.query(on: request).filter(\Post.viewCount > 500).all()
    }

The filter method uses keypaths for filtering queries, which means we get type safety. The type of the viewCount property must match what we put after the operator, or else we'll get a compiler error. Very swifty!

The response for the above route would be:

    [{"id":5,"content":"fifth post here", "created_at":"2018-03-13T00:00:00Z", "updated_at":"2018-03-13T00:00:00Z","viewCount":800,"keyword":"tech"},
    {"id":6,"content":"sixth post here", "created_at":"2018-03-12T00:00:00Z", "updated_at":"2018-03-12T00:00:00Z","viewCount":600,"keyword":"entertainment"}]

I used the > operator to retrieve only posts with a viewCount greater than 500. Here are the currently available operators for comparison while using the filter method:

  • >, greater than
  • <, less than
  • <=, less than or equals to
  • ==, equals
  • As of this writing (March 18), the >= operator is mysteriously missing and does not compile correctly. I'll update this tutorial as changes occur.

    We can also filter by the keyword property. For example, to get objects whose keyword property is just "news", you could write the following route

        func filterByKeyword(_ request: Request) throws -> Future<[Post]> {
            return Post.query(on: request).filter(\Post.keyword == "news").all()
        }
    

    The response would be:

        [{"id":2,"content":"second post here", "created_at":"2018-03-17T00:00:00Z", "updated_at":"2018-03-17T00:00:00Z","viewCount":111,"keyword":"news"},
        {"id":3,"content":"third post here", "created_at":"2018-03-15T00:00:00Z", "updated_at":"2018-03-15T00:00:00Z","viewCount":400,"keyword":"news"},
        {"id":7,"content":"seventh post here", "created_at":"2018-03-11T00:00:00Z", "updated_at":"2018-03-11T00:00:00Z","viewCount":230,"keyword":"news"}]
    

    If we wanted to get all Posts whose keyword property matched exactly "news" or "tech", we could do:

        func getNewsOrTech(_ request: Request) throws -> Future<[Post]> {
            return Post.query(on: request).filter(\Post.keyword, in: ["news", "tech"]).all()
        }
    


    Chaining filters

    In Vapor, calling the filter method creates an AND group by default. So if we wanted to fetch every Post that has both a viewCount greater than 400, and is in the category "tech", we would simply add another filter method onto the result of our previous one, like so:

        func chainFilters(_ request: Request) throws -> Future<[Post]> {
            return Post.query(on: request).filter(\Post.keyword == "tech").filter(\Post.viewCount > 400).all()
        }
    

    Our response for the above route should be:

        [{"id":5,"content":"fifth post here", "created_at":"2018-03-13T00:00:00Z", "updated_at":"2018-03-13T00:00:00Z","viewCount":800,"keyword":"tech"}]
    


    Groups

    As said above, chaining filters, by default, creates an AND group. But we can also create OR groups using Fluent as well. For example, if we wanted to fetch all Posts with a viewCount greater than 700, or whose keyword property is "news", we could write:

        func makeOrGroup(_ request: Request) throws -> Future<[Post]> {
            return Post.query(on: request).group(QueryGroupRelation.or) { (builder) in
                builder.filter(\Post.keyword == "news")
                builder.filter(\Post.viewCount > 700)
                }.all()
        }
    


    Retrieve by Date

    We may also want to retrieve all posts by only a certain date. For example, we may want to retrieve all posts that were created on or after March 13. To do so, we can write the following route:

        func getByDate(_ request: Request) throws -> Future<[Post]> {
            let day = 13, month = 3, year = 2018
            let components = DateComponents(calendar: Calendar.current, timeZone: nil, era: nil, year: year, month: month, day: day, hour: 0, minute: 0, second: 0, nanosecond: 0, weekday: nil, weekdayOrdinal: nil, quarter: nil, weekOfMonth: nil, weekOfYear: nil, yearForWeekOfYear: nil)
            guard let marchThirteenth = components.date else { throw Abort.init(HTTPStatus.badRequest, reason: "invalid date", identifier: nil)}
            return Post.query(on: request).filter(\Post.created_at > marchThirteenth).all()
        }
    

    Sort by Date

    We can also sort posts by either ascending or descending order. Descending order means the most recent posts are first in your array, while ascending is the opposite. To sort our Post class by order of descending for the created_at key, we would write:

        func sortByDate(_ request: Request) throws -> Future<[Post]> {
            let queryField = QueryField(name: "created_at") // 1
            let querySort = QuerySort(field: queryField, direction: QuerySortDirection.descending) // 2
            return Post.query(on: request).sort(querySort).all() // 3
        }
    
    At (1), we create a QueryField object and initialize it with the name of the database column by which we want to sort.
    At (2), we create the sort object, and assign it an order (either QuerySortDirection.descending or QuerySortDirection.ascending)
    At (3), we apply the sort to the query.

    That's all for this introduction to querying with Fluent 3 tutorial. Thanks for reading/merci de lire/danke furs lesen !



    Enjoy this article? Consider supporting VaporForums by following us on Twitter! Get the latest Vapor articles, tutorials, and news.