I see what you mean.
Now I have a problem to solve, which is the ability for users to define their own composable requests api.
Let's put CTE on the side for now, and define what "composable requests api" means. An example will help:
// All lead players from French teams of 1st category ordered by name:
Player.all()
.filter(role: .lead)
.filter(teamCountry: "FR")
.filter(teamCategory: .first)
.orderedByName()
All those methods are application-defined apis:
App code
extension Player {
static let team = belongsTo(Team.self)
}
extension DerivableRequest where RowDecoder == Player {
func filter(role: Player.Role) -> Self {
filter(Player.Columns.role == role)
}
func filter(teamCountry: String) -> Self {
joining(required: Player.team.filter(country: teamCountry))
}
func filter(teamCategory: Team.Category) -> Self {
joining(required: Player.team.filter(category: teamCategory))
}
func orderedByName() -> Self {
order(Player.Columns.name.collating(.localizedCaseInsensitiveCompare))
}
}
What makes those apis freely composable is the ability to "merge" common elements. See for example how the team
table is mentionned twice in the Swift request, but only once in SQL:
// SELECT player.* FROM player
// JOIN team
// ON team.id = player.teamId
// AND team.coutry = 'FR'
// AND team.category = 'first'
Player.all()
.filter(teamCountry: "FR")
.filter(teamCategory: .first)
This is no magic of course. All associations have a "key" (basically, a Swift string). Those keys serve two purposes:
-
Association keys allow the decoding of complex record types:
extension Player {
// Key is "team" (the default)
static let team = belongsTo(Team.self)
}
struct PlayerInfo: Decodable, FetchableRecord {
var player: Player
// The "team" property name matches the association key
var team: Team
}
// Yeah, it works
let infos /*: [PlayerInfo] */ = try Player
.including(required: Player.team)
.asRequest(of: PlayerInfo.self)
.fetchAll(db)
-
Association keys allow request composition.
They make it possible to recognize that the team
table should be joined only once at the SQL level:
// SELECT player.* FROM player
// JOIN team
// ON team.id = player.teamId
// AND team.coutry = 'FR'
// AND team.category = 'first'
Player
// Joins association with key "team"
.joining(required: Player.team.filter(country: "FR"))
// Joins association with key "team" again
.joining(required: Player.team.filter(category: .first))
Note how "French teams" and "Teams of 1st category" are not equal relations. We really need something else in order to recognize a common identity. This identity is the association key.
Summary:
- GRDB requests are composable.
- What makes requests involving associations composable are their association keys.
Back to CTE. Should support for them eventually come to the query builder, I want requests to remain composable.
This means that it must be possible to write a Swift request that mentions a CTE several times, just like we did with associations above, and yet generate a single WITH
clause.
Let's look at your sample code:
try Row
.with(/* some CTE */)
.filter(...)
.select(...)
.fetchOne(db)
It must be possible to use the same CTE several times:
try Row
.with(/* some CTE */)
.filter(...)
.with(/* the same CTE */)
.filter(...)
.fetchOne(db)
The generated SQL would have a single WITH
clause. The two filter expressions would be joined with a simple AND
.
So, how do we recognize that two CTEs are the same?
Problem is that a CTE like Author.select(...).filter(...)
has no identity. It has nothing like an association key. And GRDB requests do not conform to Equatable.
Requests are not Equatable
The reason is that requests are generally not defined with values, but with "value promises", which are functions that accept a database connection. Swift functions can not be compared.
For example, Player.orderByPrimaryKey()
is a request that can't generate SQL until a database connection is available, the schema can be queried, and the primary key is known:
try dbQueue.read { db in
// SELECT * FROM player ORDER BY id
try Player.orderByPrimaryKey().fetchAll(db)
// SELECT * FROM country ORDER BY code
try Country.orderByPrimaryKey().fetchAll(db)
}
I don't think requests will become Equatable. But maybe we can define CTEs in a way similar to associations, as static properties of the record types that need them, in a way that makes them identifiable.
That's my suggested exploration track.