Parent Child Relationships and Foreign Keys

Adding a Foreign Key From Issues to Projects

We first need to create a property for the projectId that the issue belongs to:

// Issue.swift
var projectId: UUID

We'll also need an initializer to provide this value. We don't want to allow Issue records without a parent Project:

init(subject: String, body: String, projectId: UUID) {
    // ...
}

Next we need to alter our migration to add this column and set the foreign key relationship:

extension Issue : Migration {
    static func prepare(on conn: PostgreSQLConnection) -> Future<Void> {
        return PostgreSQLDatabase.create(self, on: conn) { builder in            
            builder.uuidPrimaryKey()            
            builder.field(for: \.subject, type: .varchar(500))
            builder.field(for: \.body)
            builder.field(for: \.status, type: .varchar(100))

            // add the new column
            builder.field(for: \.projectId)

            // add the foreign key constraint
            builder.reference(from: \.projectId, to: \Project.id,
                              onUpdate: nil,
                              onDelete: .cascade)

            builder.timestampFields()

        }
    }
}

Notice that on the foreign key reference, we specify .cascade for the onDelete behavior. This ensures that if we delete a project, all referencing issues will be deleted as well. We don't want to allow orphaned records to be hanging around our system.

Setting up the Fluent Relationships

We need to tell our fluent models about this relationship. We'll start on the parent side, which is Project:

    var issues: Children<Project, Issue> {
        return children(\.projectId)
    }

Here we use the Children type, which has two generic parameters, From and To. This relationship goes from a project to a list of issues.

Inside the method we specify the foreign key that identifies the parent record.

On the other side, in Issue.swift:

    var project: Parent<Issue, Project> {
        return parent(\.parentId)
    }

Here we use a Parent type to specify the relationship. Again, we specify the type we're coming from and the type we're relating to.

Inside the method we call the parent method and pass the column that we're using to specify which project to load.

In the episode I mistakenly wrote the parent relationship as \.id, but it actually needs to be the foreign key column on the issues table.

Querying for a Project's Issues

Now that we have our relationships set up we can query for issues belonging to a project:

Project.find(...., on: req).flatMap { project in 
    guard let project = project else { throw Abort(.notFound) }
    return project.issues.query(on: req).all().map { issues in
        let subjects = issues.map { $0.subject }
        return "Project issues: \(subjects).joined(separator: ", "))" 
    }
}