Back

Pivot Tables for Many to Many Relationships

Episode #371 | 14 minutes | published on January 10, 2019 | Uses Xcode-10.1, swift-4.2, Vapor-3.0.8, fluent-postgresql-1.0.0

Subscribers Only

When you have a many-to-many relationship you typically rely on a join table, or what Vapor calls a Pivot table to relate the records together. In this episode we will create a relationship to allow an issue to have many tags, and also allow a tag to apply to many issues. We'll see how we can use Vapor's ModifiablePivot and Sibling types to make working with these relationships easier.

This episode is part of a series: Server-side Swift with Vapor.

1. Getting Started with Vapor 12 min

2. Vapor Routing 14 min

3. Leaf Templates 13 min

4. Nesting Templates and Partials 8 min

5. Vapor Demo: Tokenizr 21 min

6. Setting up a Database with Fluent 19 min

7. Creating, Updating, and Deleting Records with Fluent 19 min

8. Vapor Futures 20 min

9. Setting up Vapor with Postgresql 17 min

10. UUID Primary Keys 13 min

11. Timestamp Fields 4 min

12. Refactoring to Protocols 12 min

13. Parent Child Relationships and Foreign Keys 14 min

14. Pivot Tables for Many to Many Relationships 14 min

15. Vapor Controllers 15 min

16. Decoding Request Parameters 9 min

Episode Links

Vapor Docs - Siblings

New Tag Model

Here we have a new model called Tag. An Issue can have many Tags, and Tags can belong to many Issues. This means we have a many-to-many relationship.

final class Tag : UUIDModel, TimestampModel {
  // ...
}

Designing the Join Model

In order for us to have many on both sides of the relationship, we need a model that refers to the linking of these records. Sometimes these are called join tables, but Vapor calls them Pivot tables.

final class IssueTag : Pivot, UUIDModel {
    static var name: String = "issue_tag"
    typealias Left = Issue
    typealias Right = Tag

    static var leftIdKey: WritableKeyPath = \.issueId
    static var rightIdKey: WritableKeyPath = \.tagId

    var id: UUID?
    var issueId: UUID
    var tagId: UUID
}

Then we can create a migration to create this table.

extension IssueTag : Migration {
    static func prepare(on conn: PostgreSQLConnection) -> Future<Void> {
        return PostgreSQLDatabase.create(self, on: conn) { builder in
            builder.uuidPrimaryKey()
            builder.field(for: \.issueId)
            builder.field(for: \.tagId)
            builder.reference(from: \.issueId, to: \Issue.id, onUpdate: nil, onDelete: .cascade)
            builder.reference(from: \.tagId, to: \Tag.id, onUpdate: nil, onDelete: .cascade)
        }
    }
}

And don't forget to add this line to the migrations list in configure.swift.

Setting up the Sibling Relationship

Over on Issue.swift, we can add a new Siblings relationship:

var tags: Siblings<Issue, Tag, IssueTag> {
    return siblings()
}

These are specified in from, to, through order (which is how I like to think about it).

We can do the same on the Tag side:

var issues: Siblings<Tag, Issue, IssueTag> {
    return siblings()
}

Creating Sibling Records

Since we don't have any useful information in our Pivot model beyond the two keys, we can use the ModifiablePivot type, which requires an initializer:

final class IssueTag : ModifiablePivot, UUIDModel {
    // ...

    init(_ left: Issue, _ right: Tag) throws {
        issueId = try left.requireID()
        tagId = try right.requireID()
    }

    // ...
}

Given an issue and tag instance, you can relate them like this:

return issue.tags.attach(tag, on: req).flatMap { issueTag in
    print("Created issue tag: \(issueTag.id!)")
}

View on Github

Download