Fetching all fields from joined query in Vapor 3

Question

Background

Given the following sample models (chose 2 simple examples that show a 1-n relationship):

final class Company: MySQLModel {
    var id: Int?
    var name: String
}

final class Client: MySQLModel {
    var id: Int?
    var attr1: Int
    var attr2: String
    var companyId: Company.ID

    static func prepare(on connection: MySQLDatabase.Connection) -> Future<Void> {

        return Database.create(self, on: connection, closure: { builder in
            try addProperties(to: builder)
            builder.addReference(from: \.companyId, to: \Company.id, actions: .update)
        })
    }
}

Question

Is there a way to fetch & return the result of a JOINED query (e.g: Company - Client // One-to-Many) without the need of raw queries ? I tried using Query and Relationships but there's no way to fetch all of them in one try.

Ideally, the returned data would've a nested structure like the following:

Expected:

{
  "name": "Alice",
  "id": 0000000001,
  "company": {
    "id": 11111111,
    "name": "Stack Overflow"
  }
}

---

Solution (aka workaround)

I did manage to get it "working" by using an extra structure (call it Wrapper, Box, Merged, etc.) to hold all the entities and finally use makeJSON to return it inside the Controller.

    let query = try db.query(Client.self)
        .filter(\.attr1 > 123)
        .filter(\.attr2 == "abc")
    let client = try query.first()

    // client.company is just an attribute of Client that uses
    // the *parent* method to retrieve it
    if let client = client, let company = try client.company.get() {

        // others uses *children* method
        let others = try client.others.limit(5).all()

        let companyJSON = company.dictionary! // dictionary returns [String:Any] for any Encodable
        let clientJSON = client.dictionary!


        let merged = clientJSON.merging([ "company": companyJSON ], uniquingKeysWith: { (first, _) in first })
        return merged
    }

---

Final comments

Is using a wrapper entity the only way to do it (without using raw queries) ? Dealing with multi-level results would be really tedious.

Edit: I've already found a related question Is it possible to access fields in a joined table in Vapor? but the answer didn't work in the same way I intended to.

Edit2: I've recently migrated to Vapor3, hence the new code. I think Vapor2 would be the same idea but you'd have to write a bit more code since Vapor3 introduced Codable support.

Answer 1

I'm not sure if this is entirely what you are looking for, but I think I was trying to do something similar. I wanted to show a table of users and their associated auth tokens. This is set up in the standard way, having a parent-child (one-to-many) relationship set up in Fluent. I ended up doing the following which works pretty well

func getUsersHandler(_ req: Request) throws -> Future<View> {
    return User.query(on: req).all().flatMap(to: View.self) { users in
        let tokenFutures = try users.map {
            return try $0.authTokens.query(on: req).all()
        }
        return tokenFutures.flatMap(to: View.self) { tokensByUser in
            let usersAndTokens = zip(users, tokensByUser).map {
                return UserAndTokens(user: $0, tokens: $1.map { $0.token})
            }
            let listUsersCtx = ListUsersContext(usersAndTokens: usersAndTokens)
            return try req.leaf().render("users", listUsersCtx)
        }
    }
}

I'm just learning Vapor so I have no idea if this is a great solution or not. By letting the users promise resolve, then letting each user->token promise resolve, everything stays async until the end where I package all the resolved stuff together in the encodable ListUsersContext struct

Answer 2

I've been wondering this for a while, then yesterday I saw that it's been added to the documentation at some point: https://docs.vapor.codes/3.0/fluent/querying/#join

So you should be able to do something like this

Client.query(on: conn).join(\Client.companyId, to: \Company.id)
    .filter(\.attr1 > 12)
    .filter(\Company.name == "ACME")
    .alsoDecode(Company.self)
    .all()

This should return you an array of tuples (Client,Company). You should easily be able to serialise these to the JSON you desire and it won't involve

I haven't yet checked to see if multiple instances of the same Company would be equal.