How to do two JOIN query from the same table in Vapor 3 using Fluent?

Question

This is what I want to do (using postgresql):

select H."name", A."name"

FROM "Matches" M

JOIN "Teams" H ON M."homeTeamID" = H.id

JOIN "Teams" A ON M."awayTeamID" = A.id

//This will give me an error

return Matches.query(on: request)
.join(\Teams.id, to: \Matches.homeTeamID)
.alsoDecode(Teams.self)
.join(\Teams.id, to: \Matches.awayTeamID)
.alsoDecode(Teams.self)

Here is the error:

{

error: true,

reason: "table name "Teams" specified more than once"

}

Any help is appreciated! Thanks!

Answer 1

@arema, I tried to reproduce your use case and had a similar issue with Fluent. I reported the issue on Fluent's github: https://github.com/vapor/fluent/issues/563

Here is a workaround, but it's far from elegant.

// Requires conforming `Match` to hashable, Equatable.
func getMatches2Handler(_ req: Request) throws -> Future<[MatchObjects]> {
    return map(
        to: [MatchObjects].self,
        Match.query(on: req).join(\Team.id, to: \Match.homeTeamID).alsoDecode(Team.self).all(),
        Match.query(on: req).join(\Team.id, to: \Match.awayTeamID).alsoDecode(Team.self).all()
    ) { homeTuples, awayTuples in
        let homeDictionary = homeTuples.toDictionary()
        let awayDictionary = awayTuples.toDictionary()
        var matchObjectsArray: [MatchObjects] = []
        matchObjectsArray.reserveCapacity(homeDictionary.count)
        for (match, homeTeam) in homeDictionary {
            let awayTeam = awayDictionary[match]!
            matchObjectsArray.append(MatchObjects(match: match, homeTeam: homeTeam, awayTeam: awayTeam))
        }
        return matchObjectsArray
    }
}

//...

extension Array {
    func toDictionary<K,V>() -> [K:V] where Iterator.Element == (K,V) {
        return self.reduce([:]) {
            var dict:[K:V] = $0
            dict[$1.0] = $1.1
            return dict
        }
    }
}