How do I add additional information to a Pivot (using Fluent)?

Question

In Vapor, we can create many-to-many relationships by creating a Pivot<U, T> object, where U and T are the models that we want to link together. So if I want to create a system where Users can have many Files and many Files can belong to many Users, I'd associate them like this:

var alice = User(name: "Alice")
try! alice.save()
var sales = File(name: "sales.xclx")
try! sales.save()

var pivot = Pivot<User, File>(alice, sales)
try! pivot.save()

What I can't figure out for the life of me is how would I make a Pivot<User, File> contain additional information? For example, I'd like to know when was this file associated associated to Alice, or what permissions she has over it.

On a Relational database, Fluent creates this table for the Pivot<User, File> type.

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| file_id | int(11) | NO   |     | NULL    |                |
| user_id | int(11) | NO   |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

But I'd like the ability to represent something like this:

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| file_id | int(11) | NO   |     | NULL    |                |
| user_id | int(11) | NO   |     | NULL    |                |
| date    | date    | NO   |     | NULL    |                |
| perms   | varchar | NO   |     | READ    |                |
+---------+---------+------+-----+---------+----------------+

Answer 1

The Pivot<U, T> object can be thought of as the "bare minimum" required fields for a pivoted relation like siblings.

If you want to add custom fields to this table, you can create your own class to act as the pivot as long as it has the required elements:

• Table name for Foo and Bar is bar_foo (lowercase, alphabetically ordered)
• There exists at least the three columns: id, bar_id, foo_id

In other words, the table created by your pivot class must have at least the elements a Pivot<Foo, Bar> preparation would have created.

With this done, you can create new pivot relations by creating and saving instances of your pivot class.

When .siblings() relations are called on your models that use this pivot table, the default Pivot<U, T> will still be created to perform the fetch. But, this won't create any issues since the required fields are present on the pivot table.

Answer 2

so after having the same problem described by Andy and asking for a solution on the Vapor Slack I was redirected here.

My implementation (using PostgreSQL) of the solution proposed by Tanner can be found here

The key is the Rating model:

• it’s a plain Model subclass
• it has an entity name of movie_user (as described by Tanner the names of the relating models in alphabetical order)
• it has the fields userId (mapping to "user_id") and movieId (mapping to "movie_id"), both are of type Node.
• in prepare(Database) it again uses the name "movie_user" and defines the Id fields as Ints.

With that set up you can define the following relationship convenience methods:

On Movie: all raters

extension Movie {
    func raters() throws -> Siblings<User> {
        return try siblings()
    }
}

On User: all rated movies

extension User {
    func ratedMovies() throws -> Siblings<Movie> {
        return try siblings()
    }
}

A new rating for a movie (for a user) can be added like this:

ratings.post() { request in
    var rating = try Rating(node: request.json)
    try rating.save()
    return rating
}

As Rating is a Model subclass, we can create it directly from the requests JSON. This requires the client to send a JSON document that conforms to the node structure of the Rating class:

{
    "user_id": <the rating users id>,
    "movie_id": <the id of the movie to be rated>,
    "stars": <1-5 or whatever makes sense for your usecase>
}

To get all actual Ratings for a given movie, you seem to have to resolve the relationship manually (at least I think so, maybe somebody can give me a hint on how to do this better):

let ratings = try Rating.query().filter("movie_id", movieId).all()

Also, there seems to be no way of somehow calculating an average on the database right now. I would have loved something like this to work:

// this is now how it works
let averageRating = try Rating.query().filter("movie_id", movieId).average("stars")

So I hope this helps anybody coming across this problem. And thanks to all the wonderful people who contribute to the Vapor project!

Thx to @WERUreo for pointing out that the part where a rating is created was missing.