Introduction to Querying with Fluent in Vapor 2

This tutorial will cover how to query objects in a Postgres db using Vapor's ORM, Fluent.

To give you a visual representation of the data I'll be working with, here is a photo of what the database looks like:

I have a single Post class, with attributes of:

id

content

created_at, updated_at

keywords

viewCount

Querying

In Vapor, you instantiate a query from your model class like so:

let query = try YourModel.makeQuery()

The class I'm working with is Post , so I'll be using that from now on in this tutorial.

All queries (except for 1 exception) are built off of this. Let's start simple, and retrieve all posts. To do so, call:

let query = try Post.makeQuery().all()

And the other simple option: to retrieve the first object, call:

let query = try Post.makeQuery().first()

What if we know the id of an object, and want to retrieve that? This is the exception:

let query = try Post.find(4)

Simply pass in the id of the object, and call the find method.

Simple enough. Let's move on to a bit more complicated ones!

Filter

Oftentimes we only want to fetch specific posts form 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 100? Using filter, we would call:

let greaterThan100 = try Post.makeQuery().filter("viewCount", .greaterThan, 100).all()

And the response would be:

[{"id":1,"content":"this is content 1"}, {"id":6,"content":"sixth post here"}, {"id":10,"content":"thai tea is the best tea"}, {"id":9,"content":"bangkokg is the best city"}, {"id":14,"content":"fourteenth post"}, {"id":5,"content":"fifth post here"}, {"id":12,"content":"white flower factory is the best restaurant"},{"id":13,"content":"apple > google"} {"id":17,"content":"it's really hot in Bangkok today"}]

Notice, that the post with an Id of 2 is not in the results (it has exactly 100 views)--to include that, we would need to use another operator, .greaterThanOrEquals , like:

let hundredOrGreater = try Post.makeQuery().filter("viewCount", .greaterThanOrEquals, 100).all()

Vapor has other operators, as well, in addition to .greaterThan and .greaterThanOrEquals , such as:

.equals

.lessThan

.lessThanOrEquals

.notEquals

.contains

The .all() at the end of the query actually gets the results of the query and returns them as an array of [YourModel], however the .all() method itself is not actually part of the query.

There area few more as well, but the above are sufficient for the scope of this introductory tutorial.

Sort

What if we want to order our query, by time-descending (meaning, the newest posts are fetched first). We can use the sort method:

let desc = try Post.makeQuery().sort("created_at", .descending).all()

Similarly, to sort by order ascending, we could do sort("created_at", .ascending)

Use the `sort` method to change the *order* of the results of what you're retrieving from the database. Use the filter method to change *what* you actually retrieve from the database.

For example, if we want to retrieve objects whose keyword property contains the word "tech", and then order by highest -> lowest viewCount , we would do:

let query = try Post.makeQuery().filter("keywords", .contains, "tech").sort("viewCount", .descending).all()

.filter

sort

.filter

, we call, andwe call .on the result of

Our response would then be:

[{"id":9,"content":"bangkokg is the best city"},{"id":12,"content":"white flower factory is the best restaurant"},{"id":11,"content":"panang cury is the best food"}]

Which checks out, as those are the 3 posts which contain the keyword "tech", and they are correctly ordered, starting with the highest viewCount .

How about retrieving only the top 3 posts with the highest viewCount? We can use the limit method, and pass in 3 as the parameter:

let query = try Post.makeQuery().sort("viewCount", .descending).limit(3).all()

Vapor has the limit method, which is also very useful for pagination. In some implementations, you can also specify an offset as well: limit(20, offset: 5) .

Groups

In Vapor, calling the filter method, by default, creates an AND group--meaning you are specifying that your query results must match both the first call of filter AND the second call. We can also create OR groups as well.

For example, if we want to fetch all posts that have either "news" as a keywords property, OR, a viewCount higher than 150. In this case, our query would be:

let query = try Post.makeQuery().or { orGroup in try orGroup.filter("keywords", .contains, "news") try orGroup.filter("viewCount", .greaterThan, 150) }

To get more complex, we can also add an AND group within an OR group. Starting from the above example, if we want to alter it to fetch posts which either have a viewCount greater than 150, OR "news" AND "entertainment" AND "world" as a "keywords", then we would do:

let query = try Post.makeQuery().or { orGroup in try orGroup.and { andGroup in try andGroup.filter("keywords", .contains, "news") try andGroup.filter("keywords", .contains, "entertainment") try andGroup.filter("keywords", .contains, "world") } try orGroup.filter("viewCount", .greaterThan, 150) }

The result of this would yield:

[{"id":1,"content":"this is content 1"},{"id":6,"content":"sixth post here"}, {"id":10,"content":"thai tea is the best tea"},{"id":9,"content":"bangkokg is the best city"}, {"id":14,"content":"fourteenth post"},{"id":5,"content":"fifth post here"}, {"id":12,"content":"white flower factory is the best restaurant"}, {"id":13,"content":"apple > google"}]

And that's all for this introductory tutorial to querying with Vapor 2. Leave any questions/comments below. Thanks for reading!