Problem using belongsTo

Related Projects GRDB
1

I'm new to GRDB and trying to understand how to make a fairly simple left join work. I'm loading data from a source I have no control over, so I'm trying to keep things simple by avoiding nullable fields in my model types.

I'm having problems with .belongsTo not creating the related object.

Imagine a Book with a foreign key category_id:

struct Book: Codable, Sendable, FetchableRecord, PersistableRecord, Equatable {
  public let bookId: String
  public let categoryId: String

  // I'd like GRDB to map this to a valid category object after loading
  public var category: Category?

  enum CodingKeys: String, CodingKey {
        case bookId = "book_id"
        case categoryId = "category_id"
  }
    static let categoryColumn = Column(CodingKeys.categoryId.rawValue)
    static let categoryForeignKey = ForeignKey([categoryColumn])
    static let category = belongsTo(Category.self, using: categoryForeignKey).forKey("category")

    public init(from decoder: Decoder) throws {
        let container = try decoder.container(keyedBy: CodingKeys.self)
        self.bookId = container.decodeString(forKey: .bookId)
        self.categoryId = container.decodeString(forKey: .categoryId)
  }
}

struct Category: Codable, Sendable, FetchableRecord, PersistableRecord, Equatable {
  public let name: String
  public let categoryId: String
  public var category: Category?

  enum CodingKeys: String, CodingKey {
        case categoryId = "category_id"
        case name
  }
}

When querying, I use this, which appears to be loading all the data:

            let all = try Book.all()
                .including(optional: Book.category)
                .asRequest(of: Book.self) // Not sure if this is even needed
                .fetchAll(database)

I would expect category to be loaded if there's a valid item with a matching category id. Instead, it's always null. I don't see any obvious errors in the logs, and I confirmed the SQL query is including the left outer join.

Interestingly, this works, but I'd rather not have to deal with yet another type.

struct BookWithCategory: : FetchableRecord, Decodable {
    
    let book: Book
    let category: Category?
}

            let all = try Book.all()
                .including(optional: Book.category)
                .asRequest(of: BookWithCategory.self)
                .fetchAll(database)
2

Hello @PatrickDanino,

I would expect category to be loaded if there's a valid item with a matching category id. Instead, it's always null.

Yes: your implementation of Book.init(from: decoder) does not set the category property. It won't populate itself.

Here Codable is not the best ally. Providing a custom implementation of the FetchableRecord and PersistableRecord conformances is probably better:

Sample code 
struct Category: Codable {
    var id: Int64
    var name: String
}

extension Category: FetchableRecord, PersistableRecord {
    // Conformance derived from Codable
}

struct Book {
    var id: Int64
    var title: String
    var categoryId: Int64
    var category: Category?
}

extension Book: FetchableRecord, PersistableRecord {
    // Do not derive the conformance from Codable, because we perform a
    // quite special handling of category.

    // Recommended practice: columns are declared in a
    // dedicated `Columns` enum.
    enum Columns {
        static let id = Column("id")
        static let title = Column("title")
        static let categoryId = Column("categoryId")
    }
    
    static let categoryKey = "category"
    static let category = Book.belongsTo(Category.self).forKey(categoryKey)
    
    init(row: Row) throws {
        self.id = row[Columns.id]
        self.title = row[Columns.title]
        self.categoryId = row[Columns.categoryId]
        self.category = row[Self.categoryKey]
    }
    
    func encode(to container: inout PersistenceContainer) throws {
        container[Columns.id] = id
        container[Columns.title] = title
        container[Columns.categoryId] = categoryId
    }
}

try DatabaseQueue().write { db in
    try db.create(table: "category") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    
    try db.create(table: "book") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("title", .text).notNull()
        t.belongsTo("category").notNull()
    }
    
    let bigCategory = try Category(id: 1, name: "Big").inserted(db)
    let smallCategory = try Category(id: 2, name: "Small").inserted(db)
    try Book(id: 1, title: "Little Blue and Little Yellow", categoryId: smallCategory.id).insert(db)
    try Book(id: 2, title: "Moby-Dick", categoryId: bigCategory.id).insert(db)
    
    let books = try Book
        .including(required: Book.category)
        .fetchAll(db)
    print(books)
}

Interestingly, this works, but I'd rather not have to deal with yet another type.

Despite this preference, take the time to check the recommended practices: Recommended Practices for Designing Record Types.

3

Hi,

Thanks for the response! Just to clarify behaviour and what's in the docs, does defining a Columns enum have any impact on fetching and/or persisting (similar to how CodingKeys works), or is that just for clarity when referencing Columns throughout the code?

As you accurately pointed out, the property category isn't in the coding keys, but that's primarily to allow this model to be correctly decoded from a JSON response.

Separately, I was under the impression that using .forKey(“category”) as part of the belongsTo was sufficient for it to populated from within Book? I did notice the docs for forKey create a separate type EmployeeInfo , which is what made me try it out, but the docs don't make it clear why, nor is there an explanation as to why you couldn't get the same behaviour directly from Employee if you were to query for that instead.

It’s also a bit surprising that there’s no warning of any kind that I could see, even with tracing enabled. I do appreciate very much the help, I'll try this again, and may resort to using a separate type if it helps simplify things.

4

The nested Columns enum is there for clarity, but also to fuel the SQL builder. Compare:

// With the nested Columns enum (assuming Book.Columns.title is defined)
let books = try Book.order(\.title).fetchAll(db)

// With other conventions
let book = try Book.order(Book.titleColumn).fetchAll(db)

[...] that's primarily to allow this model to be correctly decoded from a JSON response.

The same data is not organized in the same way in a JSON file, which can nest objects, and in database tables, which are flat. To avoid the problems that this mismatch can create, some apps define two independent sets of models: one for the JSON representation, and one for the database. The advantage is twofold: both sets of models can be tuned for their dedicated use, and they can evolve independently over time and app versions.