Vapor 4: how to include siblings including extra properties from the pivot table?

Question

I am struggling a lot with how to return a model that contains a many-to-many relationship via a pivot table that contains extra fields. Basically, I want to return the full pivot table with the extra fields, but I can't figure how to do this.

Let's consider the following 3 models: Course, User, and the pivot between them, a Student. The Student model contains the extra field progress.

final class Course: Model, Content {
  static let schema = "courses"

  @ID(key: .id)
  var id: UUID?

  @Field(key: "name")
  var name: String

  init() { }
}

final class Student: Model {
  static let schema = "students"

  @ID(key: .id)
  var id: UUID?

  @Parent(key: "course_id")
  var course: Course

  @Parent(key: "user_id")
  var user: User

  @Field(key: "progress")
  var progress: Int

  init() { }
}

final class User: Model, Content {
  static let schema = "users"

  @ID(key: .id)
  var id: UUID?

  @Field(key: "name")
  var name: String

  @Field(key: "private")
  var somePrivateField: String

  init() { }
}

I have a route like this, which returns an array of courses:

func list(req: Request) throws -> EventLoopFuture<[Course]> {
  return Course
    .query(on: req.db)
    .all()
    .get()
}

The resulting JSON looks something like this:

[
  {
    "id": 1,
    "name": "Course 1"
  }
]

How can I also include the array of students, so that the end result is something like this?

[
  {
    "id": 1,
    "name": "Course 1",
    "students": [
      {
        "user": {
          "id": 1,
          "name": "User 1"
        },
        "progress": 0
      },
      {
        "user": {
          "id": 2,
          "name": "User 2"
        },
        "progress": 100
      },
    ]
]

I can add the users to the Course model like this:

@Siblings(through: Student.self, from: \.$course, to: \.$user)
public var users: [User]

And then change my route like this:

func list(req: Request) throws -> EventLoopFuture<[Course]> {
  return Course
    .query(on: req.db)
    .with(\.$user)
    .all()
    .get()
}

But that only adds the user info to the result, NOT the extra properties on the pivot table (namely, progress). It kinda seems to me that even though pivot tables can have extra properties and the docs even specifically point that out, there are no good ways of actually dealing with this scenario since @Siblings don't point to the pivot at all.

Bonus question: I'd want the User model be mapped to a PublicUser model, so that private/internal fields are not part of the JSON result. See this question for what I mean. I want to do that same thing, but with the Student pivot's User model. Complicated, I know 😬

Answer 1

I encountered the same issue about accessing additional fields in the pivot table, but there is a fairly tidy way of accomplishing this. In addition to your siblings relationship, define a @Children relation from Course to Student. Then, in your query, do a nested with.

Put this in your Course model:

@Children(for:\.$course) var students: [Student]

Query:

let query = Course.query(on: req.db).with(\.$students){ $0.with(\.$user) }.all()

The first with gets the additional fields of the pivot table and then the nested with get the User model.