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
}

(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
}

After that, write your request:

let requestString = "SELECT ParentID, SUM(Value) FROM child GROUP BY ParentID"

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""#

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

Finally, make the query:

return sqlDatabase.raw(SQLQueryString(requestString)).all(decoding: MyQueryResult.self)

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. ↩