Categories
Technology

Custom Queries in Vapor Fluent

While the QueryBuilder interface is pretty neat, it’s still missing some things. Recently, I needed a GROUP BY clause in something, and was rather unsurprised to find that Fluent doesn’t support it.1

Fortunately, it’s still possible to write custom SQL and read in the results. Make yourself a convenience struct to unpack the results:

struct MyQueryResult: Codable { let parentID: Parent.IDValue let sum: Double }
Code language: Swift (swift)

(Strictly speaking, it can be Decodable instead of Codable, but as long as the Parent.IDValue (generated for free by making Parent conform to Model, I believe) is Codable, Swift generates the conformance for us.)

Now, in your controller, import SQLKit, and then get your database instance as an SQL database instance:

guard let sqlDatabase = req.db as? SQLDatabase else { // throw or return something here }
Code language: Swift (swift)

After that, write your request:

let requestString = "SELECT ParentID, SUM(Value) FROM child GROUP BY ParentID"
Code language: Swift (swift)

Note – your syntax may vary; I found that, using Postgres, you need to wrap column names in quotes, so I used a neat Swift feature to make that less painful:

let requestString = #"SELECT "ParentID", SUM("Value") FROM child GROUP BY "ParentID""#
Code language: Swift (swift)

If you want to use string interpolation, swap out \() for \#().

Finally, make the query:

return sqlDatabase.raw(SQLQueryString(requestString)).all(decoding: MyQueryResult.self)
Code language: Swift (swift)
  1. Entity Framework Core, which is an incredibly robust, full-featured ORM, only barely supports GROUP BY, so seeing this rather young ORM not support it isn’t all that shocking.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.