Swift Vapor Fluent: How do I query two joins

I am using Vapor 4 and I have the following MySQL Database structure: A City has reference to a POI and a POI has a reference to a Translation. How should I use the join statement. So that I get the Translation in the city model?

public final class City: Model {
    public static let schema = "cities"

    @ID(custom: "id")
    public var id: Int?
    @Parent(key: "poi_id")
    public var poi: POI
}

public final class POI: Model {
    public static let schema = "b_pois"

    @ID(custom: "id")
    public var id: Int?
    @OptionalParent(key: "name_translation_id")
    public var nameTranslation: Translation?
}

public final class Translation: Model {
    public static let schema = "b_translations"

    @ID(custom: "id")
    public var id: Int?

    @Field(key: "english")
    public var english: String

    @OptionalField(key: "german")
    public var german: String?
    ...
}

When I use the following query I expect the city model to have the value:

let cities = try await City.query(on: dataBase)
            .join(POI.self, on: \POI.$id == \City.$poi.$id)
            .join(Translation.self, on: \POI.$nameTranslation.$id == \Translation.$id)
            .with(\.$poi)
            .filter(City.self, \.$population >= population)
            .all()
// expected:
cities.first!.poi.nameTranslation != nil
// or
cities.first!.$poi.$nameTranslation.value != nil

But even though the database has a value and poi ist not nil I don't retrieve a value for the name translation. Am I missing something?

I need something like:

.with(\.$poi.$nameTranslation)

But this is not possible.

Solution

You can achieve what you want by nesting with. Something like:

 .with(\.$poi) { $0.with(\.$nameTranslation) }

Should do the job nicely!

You don't need the explicit JOINs if you are using Parent/OptionalParent, etc. Let fluent take care of it. You can then access the translated name (with suitable guard/if let, etc.):

city.poi.nameTranslation

Your query should boil down to:

let cities = try await City.query(on: dataBase)
            .with(\.$poi) { $0.with(\.$nameTranslation) }
            .filter(City.self, \.$population >= population)
            .all()

In vapor terms, this is called 'nested eager loading' and is documented here.