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

You'd query that easily with raw SQL query or using SwifQL lib

Here is an example with SwifQL

struct TeamWithUsers: Content {
    let id: UUID
    let name, icon: String
    let users: [User]
}

func getCategoriesWithProducts(_ req: Request) throws -> Future<[TeamWithUsers]> {
    return try req.parameters.next(Event.self).flatMap { event in
        let usersSubquery = SwifQL
            .select(Fn.coalesce(Fn.array_agg(Fn.to_jsonb(User.table)), PgArray() => .jsonbArray))
            .from(User.table)
            .where(\User.teamID == \Team.id)
        let query = try SwifQL
            .select(\Team.id, \Team.name, \Team.icon, |usersSubquery | => "users")
            .from(Team.table)
            .where(\Team.eventID == event.requireID())
        // here you could print the raw query for debugging
        // print(query.prepare(.psql).plain)
        return query.execute(on: req, as: .psql).all(decoding: TeamWithUsers.self)
    }
}

Answer 2

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
}