Vapor 3 and Fluent - nested query

Question

I'm trying to do a nested query in Vapor 3 and Fluent. The point is I need to get all the users, from each team where the teams have a specific eventID. Teams are children of Event. Users are children of Teams. Thanks in advance for your help. There are only 15 teams per event, but 12 users per team

Here is the Event model:

final class Event: Codable {    
    var id: Int?
    var name: String
}

extension Event {
    var teams: Children<Event, Team> {
        return children(\.eventID)
    }
}

Here is the Team model

final class Team: Codable {    
    var id: Int?
    var name: String
    var icon: String
    var eventID: Event.ID
}

extension Team {
    var user: Parent<Team, Event> {
        return parent(\.eventID)
    }
}

extension Team {
    var users: Children<Team, User> {
        return children(\.teamID)
    }
}

Here is the User model.

final class User: Codable {
    var id: UUID?
    var name: String
    var email: String
    var eventID: Event.ID
    var teamID: Team.ID
}

extension User {
    var user: Parent<User, Team> {
        return parent(\.teamID)
    }
}

I need to send an event ID and I want it to return all the users in all the teams

func getUsersForEvent(_ req: Request) throws -> Future<[User]> {        
    return try req.parameters.next(Event.self).flatMap(to: [User].self) { event in
        return try event.teams.query(on: req).all().flatMap(to: [User].self) { team in
            return try team.users.query(on: req).all()
        }
    }
}

Answer 1

Here's what I came up with, with help from the Ray Wenderlich book. In my task I don't need to return all the users and only need to see teams for 1 event at a time, so I pass in the eventID as a parameter.

Any guidance on how to sort the result by teamScore?

func getTeamsWithUsersForEvent(_ req: Request) throws -> Future<[TeamWithUsers]> {
    let currentID =  try req.parameters.next(Int.self)
    print("currentID \(currentID)")
    return Team.query(on: req).filter(\Team.eventID == currentID).all().flatMap(to: [TeamWithUsers].self) { team in
        try team.map { team in
            try team.users.query(on: req).all().map { users in
                TeamWithUsers(
                    id: team.id,
                    name: team.name,
                    icon: team.icon,
                    eventID: team.eventID,
                    //rawScore: team.rawScore,
                    //users: users,
                    count: users.count,
                    teamScore: team.rawScore / users.count
                )
            }
        }.flatten(on: req)
    }
}

struct TeamWithUsers: Content {
    let id: Int?
    let name: String
    let icon: String
    let eventID: Event.ID
    //let rawScore: Int
    //let users: [User]
    let count: Int
    let teamScore: Int
}