vapor
`@Parent` reference with `@CompositeID` cannot be queried properly
Describe the bug
If the Parent property on a child is using CompositeID, it cannot be queried properly.
To Reproduce
final class ParentModel: Model {
static var schema = "parents"
final class Identifier: Fields, Hashable {
@Field(key: "id1")
var id1: String
@Field(key: "id2")
var id2: Int
func hash(into hasher: inout Hasher) {
hasher.combine(id1)
hasher.combine(id2)
}
static func == (lhs: ParentModel.Identifier, rhs: ParentModel.Identifier) -> Bool {
lhs.id1 == rhs.id1 && lhs.id2 == rhs.id2
}
}
@CompositeID
var id: Identifier?
@Children(for: \.$parent)
var children: [ChildModel]
}
final class ChildModel: Model {
static var schema = "children"
@ID(custom: "id")
var id: String?
@Parent(key: "parent")
var parent: ParentModel
}
By using such model layout, FluentKit tries to load ChildModel with:
SELECT `children`.`id` AS `children_id`, `children`.`parent` AS `children_parent` FROM `children`
which is not ideal and always fails.
Expected behavior
The correct query for the example should be:
SELECT `children`.`id` AS `children_id`, `children`.`parent_id1` AS `children_parent_id1`, `children`.`parent_id2` AS `children_parent_id2` FROM `children`
Environment
- FluentKit version: 1.28.0
- OS version: macOS 12.4
- Database backend: MySQL 8.0
Additional context
Add any other context about the problem here.
It is not currently possible to really support this properly, because the @Parent property would have to accept an array of keys to correspond to each column of the parent model's composite ID. This could possibly be done by creating an additional @CompositeParent property type (and corresponding @OptionalCompositeParent), but before doing the necessary additional work for that, I'd ask what the use case is for having a composite @Parent to begin with.
In a real-world case, we could have a couple of offices referenced by city+number, and a number of employees each tied to one office.
Of course this could be resolved by adding a redundant UUID for each office when designing the layout, but for existing databases we can’t easily do this, and it is a design overhead we should avoid.
I would argue that such a use case is not a compelling one for the use of a multi-column primary key to start with - the location information of an office makes for a poor candidate key (due at the very least to its potentially excessive length and lack of guaranteed uniqueness).
That being said, in the real world, as we all know, most databases are not carefully designed in full accordance with the principles of relational data modeling (I don't think I've yet seen anything that uses Fluent which also has a DBA designing the models 😆). I'm about to post a PR which explicitly disallows the use of @Parent etc. with composite ID targets so your attempted usage will give a useful error immediately rather than an invalid query at the point of use; if I get a chance later, I'll look into finding a relatively clean way to provide @CompositeParent et al (though honestly, I wouldn't hold my breath - if I had property wrapper requirements in protocols and could require Swift 5.7, it could probably be done nicely, but I'm not convinced it's possible as matters stand).
Note for others reading this issue: The comments here apply to #512 as well.