Foreign Key Definitions

[blevasseur-godaddy]

Staging an example of a simple foreign key One to Many relationship, it would seem I don't quite understand from a GRDB interface perspective how the values can properly encode/decode each other at the appropriate life cycle events. I'm having issues with a situation similar to the below where I can't seem to extract the expected information. When keys are implied for the relationship, I'm especially unsure how to query for these values in a cascade manner. I'm not sure if I have to query for each individual relationship outside of the decoding mechanics (which I'm hoping I don't).

import UIKit

public struct Forum: Identifiable, Codable {
    public let id: String
    public let participants: [Participant]
    public let date: Date
}

public struct Participant: Equatable, Codable {
    public let forumId: Forum.ID
    public let firstName: String?
    public let lastName: String?
}

typealias ColumnKey = CodingKey, CaseIterable, ColumnExpression

extension Forum {
    enum Column: String, ColumnKey {
        case id
//        case participants //How is this association made
        case date
    }
    static let participantsAssociation = hasMany(Participant.self)
}

extension Participant {
    enum Column: String, ColumnKey {
        case forumId
        case firstName
        case lastname
        case date
    }
}

extension Forum {
    public init(row: Row) {
        self.init(
            id: .init(row[Column.id]),
//            participants: row[Column.participants.rawValue], //How is this association made and how do I decode an associated object from the DB here
            date: row[Column.date]
        )
    }
}

//Note the following in the One to Many relationship
//Forum -> Participant?
//columnDefinition.references(Participant.self, deferred: true)
//Participant -> Forum?
//columnDefinition.notNull().references(Forum.self)

I've tried numerous query permutations similar to the below where assertions are made for columns that exist or foreign key mismatch configurations.

                    let forum = try Forum.filter(key: id.rawValue)
                        .including(all:Forum.participantsAssociation)
                        .asRequest(of: Conversation.self)
                        .fetchOne(database)

I understand that some of these values are string based and interpreted/inferred from other things (like table names), but the documentation fails to distinguish what's being referenced in quite a few situations (unfortunately).

    // MARK: - Extracting Records
    
    /// Returns the record associated with the given scope.
    ///
    /// For example:
    ///
    ///     let request = Book.including(required: Book.author)
    ///     let row = try Row.fetchOne(db, request)!
    ///
    ///     print(Book(row: row).title)
    ///     // Prints "Moby-Dick"
    ///
    ///     let author: Author = row["author"]
    ///     print(author.name)
    ///     // Prints "Herman Melville"
    ///
    /// Associated records stored in nested associations are available, too:
    ///
    ///     let request = Book.including(required: Book.author.including(required: Author.country))
    ///     let row = try Row.fetchOne(db, request)!
    ///
    ///     print(Book(row: row).title)
    ///     // Prints "Moby-Dick"
    ///
    ///     let country: Country = row["country"]
    ///     print(country.name)
    ///     // Prints "United States"
    ///
    /// A fatal error is raised if the scope is not available, or contains only
    /// null values.
    ///
    /// See <https://github.com/groue/GRDB.swift/blob/master/README.md#joined-queries-support>
    /// for more information.
    public subscript<Record: FetchableRecord>(_ scope: String) -> Record {
        try! decode(Record.self, forKey: scope)
    }

As an example, the above uses a string literal value for "author" without denoting how/where that's derived from with the required restriction being implied as an Association.

[groue]

Hello @blevasseur-godaddy,

The relationship between 1. requests that involve associations, 2. rows fetched from those requests, and 3. the records that are initialized from those rows is still not clearly explained in the documentation, I agree. I hope it will get better.

Meanwhile, some of your intuitions are correct. The first is that between the request and the decoded records, there are rows, and rows are fully based on strings: strings for column names, and strings for accessing associated records. The second is that strings that access associated records are inferred from table names (when they are not explicitly provided by the code).

Some of your intuitions are not. Your Forum struct is a record type that matches a database table, with one property per database column. So far, so good. It also has a participants property, which matches an association. Merging these two concerns in a single type is not a recommended practice. You won't find such an example in the GRDB documentation or good practices. You can see #1084 for an exploration of the problem this creates, and why this this practice should be avoided.

I'm not sure how to address your questions, but let's focus on:

As an example, the above uses a string literal value for "author" without denoting how/where that's derived from with the required restriction being implied as an Association.

The "author" string is the association key of the Book.author association.

Consider the pipeline:

1. Build a request that involve associations: Book.including(required: Book.author)
2. Fetch rows from this request
3. Initialise records from those rows

At step 1, the request involves the Book.author association (which comes with its association key). This key is transferred onto the next step:

At step 2, each row contains book columns, and author columns. If each row is built from a flat list of SQLite (column, value) pairs, a row itself is not a flat container. Author columns can be accessed through a scope named after the association key.

At step 3, the record can decode a book from the row, and an author by accessing the scope named after the association key.

The GRDB game is to make sure that steps 1 and 3 agree on the string that should be used to access author columns. A lot of efforts have been put so that this agreement is most of the time both implicit and correct. Since real life is full of surprises, the developer is always able to be explicit (we'll see some example below).

Step 1 uses the association key, which is the table name of the associated record, unless overridden:

// ----
struct Author: TableRecord { }
print(Author.databaseTableName) // "author"

struct Book: TableRecord {
    static let author = belongsTo(Author.self) // key "author"
}

// ----
struct Author: TableRecord {
     static let databaseTableName = "person"
}

struct Book: TableRecord {
    static let author = belongsTo(Author.self) // key "person"
}

// ----
struct Author: TableRecord { }
print(Author.databaseTableName) // "author"

struct Book: TableRecord {
    static let writer = belongsTo(Author.self).forKey("writer") // key "writer"
}

Step 3 uses coding keys for Codable records:

// ----
struct BookInfo: Decodable, FetchableRecord {
    var book: Book
    var author: Author // Coding key must match the association key
}
try Book
    .including(required: Book.author)
    .asRequest(of: BookInfo.self)
    .fetchAll(db)

// ----
struct BookInfo: Decodable, FetchableRecord {
    var book: Book
    var writer: Author // Coding key must match the association key
}
try Book
    .including(required: Book.author.forKey("writer")) // Adapt association key to the coding key
    .asRequest(of: BookInfo.self)
    .fetchAll(db)

// ----
struct BookInfo: Decodable, FetchableRecord {
    var book: Book
    var writer: Author // Coding key must match the association key
    enum CodingKeys: String, CodingKey {
        case book
        case writer = "author" // Adapt coding key to the association key
    }
}
try Book
    .including(required: Book.author)
    .asRequest(of: BookInfo.self)
    .fetchAll(db)

Step 3 uses row scopes for records that provide their own implementation for init(row:):

struct BookInfo: FetchableRecord {
    var book: Book
    var author: Author
    init(row: Row) {
        self.book = Book(row: row)
        self.author = row["author"] // must match the association key
    }
}
try Book
    .including(required: Book.author)
    .asRequest(of: BookInfo.self)
    .fetchAll(db)

The low-level way to write the previous initializer is:

struct BookInfo: FetchableRecord {
    var book: Book
    var author: Author
    init(row: Row) {
        self.book = Book(row: row)
        self.author = Author(row: row.scopesTree["author"]!) // must match the association key
    }
}
try Book
    .including(required: Book.author)
    .asRequest(of: BookInfo.self)
    .fetchAll(db)

When you wonder what's in the guts of a row, check Debugging Request Decoding

[groue]

The Joining And Prefetching Associated Records documentation chapter was enhanced in GRDB 5.16.0. I hope it clarifies some obscure points.