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

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)

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