FatalError when fetching associated records using dedicated record type

[juliensagot]

Hi 👋
I'm trying to use GRDB on a new project but I came across an issue.

I'm trying to join and prefetch associated records.
Per the documentation , I created a dedicated record type but when fetching with this new record type, I get an error.
My use-case is very similar to the AuthorInfo sample in the documentation but I can't get it to work.

My schema is very simple:

What I'm trying to achieve is to fetch every Exercise with their associated MuscleGroup through the PrimaryMuscleGroup table.

Types declaration:

struct MuscleGroup: Codable, Identifiable, TableRecord, FetchableRecord, MutablePersistableRecord {
    var id: String
  
    static let primaryMuscleGroups = hasMany(PrimaryMuscleGroup.self)
    static let primaryExercises = hasMany(
      	Exercise.self, 
      	through: primaryMuscleGroups, 
      	using: PrimaryMuscleGroup.exercise
    )
}

struct Exercise: Codable, Identifiable, TableRecord, FetchableRecord, MutablePersistableRecord {
    var id: String
  
    // Primary Muscle Groups
    static let primaryMuscleGroups = hasMany(
        MuscleGroup.self,
        through: Exercise.hasMany(PrimaryMuscleGroup.self),
        using: PrimaryMuscleGroup.muscleGroup
    )
    .forKey("primaryMuscleGroups")
}

struct PrimaryMuscleGroup: Codable, TableRecord, FetchableRecord, MutablePersistableRecord {
    var muscleGroupId: String
    var exerciseId: String
  
    static let muscleGroup = belongsTo(MuscleGroup.self)
    static let exercise = belongsTo(Exercise.self)
}

Schema declaration:

// 2. Define the database schema
try dbQueue.write { db in
    try db.create(table: "muscleGroup") { table in
        table.column("id", .text).notNull().unique()
        table.primaryKey(["id"])
    }
    try db.create(table: "exercise") { table in
        table.column("id", .text).notNull().unique()
        table.primaryKey(["id"])
    }
    try db.create(table: "primaryMuscleGroup") { table in
        table.column("muscleGroupId", .integer).notNull().indexed().references("muscleGroup", onDelete: .cascade)
        table.column("exerciseId", .integer).notNull().indexed().references("exercise", onDelete: .cascade)
        table.primaryKey(["muscleGroupId", "exerciseId"])
    }
}

Fetching results

try dbQueue.read { db in
    struct CompleteExercise: Decodable, FetchableRecord {
        var exercise: Exercise
        var primaryMuscleGroups: [MuscleGroup]
    }

    let results = try Exercise
        .including(all: Exercise.primaryMuscleGroups)
        .asRequest(of: CompleteExercise.self)
        .fetchAll(db)
} 

When fetching results, I get a FatalError:

The data couldn’t be read because it is missing. ("No such key: exercise or primaryMuscleGroups")

What am I doing wrong? I've been looking at the documentation for hours now but I must be overlooking something.

[groue]

Hello @juliensagot,

I don't know if this is a GRDB bug, or if your program behaves as expected - to answer this I'll have to think a little more 😅

The cause of the issue is there:

struct Exercise: Codable, Identifiable, TableRecord, FetchableRecord, MutablePersistableRecord {
    var id: String
  
    // Primary Muscle Groups
    static let primaryMuscleGroups = hasMany(
        MuscleGroup.self,
        through: Exercise.hasMany(PrimaryMuscleGroup.self), // <- 1
        using: PrimaryMuscleGroup.muscleGroup
    )
    .forKey("primaryMuscleGroups")                          // <- 2
}

1. Exercise.hasMany(PrimaryMuscleGroup.self) defines an association with key "primaryMuscleGroups" (the default key of an association is the table name - pluralized in case of to-many associations).
2. .forKey("primaryMuscleGroups") defines an association with key "primaryMuscleGroups" through the previous association.

So "primaryMuscleGroups" is the key for two different things: the associated rows from the primaryMuscleGroup table (1), or the associated rows from the muscleGroup table (2).

Are you confused? I surely am 😉

Right now, my suggestion is to lift the ambiguity and rename the intermediate association:

struct Exercise: Codable, Identifiable, TableRecord, FetchableRecord, MutablePersistableRecord {
    var id: String
  
    // Primary Muscle Groups
    static let primaryMuscleGroups = hasMany(
        MuscleGroup.self,
        through: Exercise
            .hasMany(PrimaryMuscleGroup.self)
            .forKey("ignored") // Avoid conflict with Exercise.primaryMuscleGroups
        using: PrimaryMuscleGroup.muscleGroup
    )
    .forKey("primaryMuscleGroups")
}

This change fixes your issue.

You may also find another name for your inner table "primaryMuscleGroup", so that it not named like the primaryMuscleGroups Swift arrays that contains values from another table ("muscleGroup"). For example, rename this table to "exerciseMuscleGroup", since it refers to the "exercise" and "muscleGroup" tables. This would remove the association key conflict.

---

Thank you very much for the report! 🙂

The workarounds will have your app run, and I'll have the time to look at this particular setup more closely!

[juliensagot]

Wow! Didn't expect such a prompt answer (especially on a Sunday afternoon)!
Thank you @groue, this was indeed the issue.
Your proposed solution to rename the key of the intermediate association works like a charm! 👌
Thanks again for your help, I really appreciate it. 🫶

[groue]

You're welcome :) I investigated a little bit more. Next GRDB version (6.0.0-beta.5 or 6.0.0) will make your initial code work as is.

Well, not exactly: in the sample code, the "primaryMuscleGroup" has integer foreign keys that target text primary keys.

For integer-looking string keys such as "1" or "456" (SQLite typing is subtle), this prevents associated records from matching together, and the CompleteExercise.primaryMuscleGroups array is always empty.

Modifying the "primaryMuscleGroup" table so that its columns are text fixes this for good.

[juliensagot]

Sweet!
As for the issue of the types, this is a silly error when I extracted the schema so it can be shared with you!
Many thanks @groue 🙏