How to fetch my associated data correctly (without looking in the 'unadaptated' array)

Related Projects GRDB
1

Hello, I am trying to fetch from associations. I tried to follow what is said in the GRDB documentation on this topic, but it does not work..

Brief overview, I have a Card, SubCard and WordBox models. WordBox has a polymorphic relationship with Card and SubCard. Card has a hasOne association with WordBox. SubCard has a hasMany association with WordBox

To get going, I just want to fetch both Card and its associated WordBox (which I call header in my code).

I defined a simple CardWithHeader struct

struct CardWithHeader: FetchableRecord, Decodable {
var card: Card
var header: WordBox
}

And then I tried to fetch my CardWithHeader array in this variable:

let fetchedCards: [CardWithHeader] = try appDatabase.reader.read { db in
    db.trace {
        os_log("%{public}@", log: OSLog(subsystem: Bundle.main.bundleIdentifier!, category: "SQL"), type: .debug, String(describing: $0))
    }
    var request = Card
        .including(required: Card.header)
        .asRequest(of: CardWithHeader.self)
        .order(Card.Columns.id.desc)
        .limit(limit, offset: offset)
    return try request.fetchAll(db)
}

My issue is that my CardWithHeader struct correctly initializes its card member but not its header member

I tried to analyze this through the debugger, and printed the fetched GRDB.row with which the CardWithHeader is initialized and I got this:

(lldb) po row
▿ [id:16866 headerID:232140 type:"noun" shoresh:"טפטף" group:NULL tags:"[]" metadata:"{\"url\":\"/dict/6637-tiftuf/\",\"gender\":\"M\"}"]
  unadapted: [id:16866 headerID:232140 type:"noun" shoresh:"טפטף" group:NULL tags:"[]" metadata:"{\"url\":\"/dict/6637-tiftuf/\",\"gender\":\"M\"}" id:232140 wordBoxableID:16866 wordBoxableType:"card" pronouns:"[הוא]" menukad:"טִיפְטוּף" transcription:"tiftuf"]
  - wordBox: [id:232140 wordBoxableID:16866 wordBoxableType:"card" pronouns:"[הוא]" menukad:"טִיפְטוּף" transcription:"tiftuf"]

Question 1:
So I managed to make my code work by using the unadapted field from this row and creating a custom init function for my CardWithHeader struct, but i feel this is too artificial.
From my research, I should use the annotated method to flatten my fetched columns, but I do not get how this works..

Edit:
Question 2:
Basically, here is my whole goal.
I want to be able to define a struct named FullCard that would contain all Card related informations which means:

  • card columns
  • card.header columns (header is an entry of table wordbox: card => hasOne => header)
  • card.header.translations (translations are entries of the table translation: wordbox => hasMany => translation)
  • card.subcards columns (subcard are entries of table subcard: card => hasMany => subcard)
  • card.subcards.wordbox columns (subcard => hasMany => wordbox. wordbox have a polymorphic relationship with card and subcard)
  • card.subcards.wordbox.translations columns (wordbox => hasMany => translation)
    This is where i would like to end up.
    All of these relationships are correctly defined in my models. My issue is really on how to correctly design the structs that will receive the fetched data and how to fetch the data.

Here are my Card and WordBox structs:

struct Card: Codable {
    var id: Int64?
    var headerID: Int64?
    var shoresh: String? = nil
    var group: CardGroup? = nil
    var tags: [String] = []
    var type: CardType
    var metadata: [String: String] = [:]
    
    var wrappedShoresh: String {
        return shoresh ?? ""
    }

    var wrappedGroupDescription: String {
        return group?.description ?? ""
    }
    
    var wrappedGroupHebrew: String {
        return group?.hebrew ?? ""
    }
}

extension Card: FetchableRecord, MutablePersistableRecord {
    static var databaseTableName: String {
        return "card"
    }
    
    enum Columns: String, ColumnExpression {
        case id, headerID, shoresh, group, tags, type, metadata
    }
    
    static let subcards = hasMany(SubCard.self)
    var subcards: QueryInterfaceRequest<SubCard> {
        return request(for: Card.subcards)
    }
    
    static let header = hasOne(WordBox.self, using: ForeignKey(["id"], to: ["headerId"]))
    var header: QueryInterfaceRequest<WordBox> {
        return request(for: Card.header)
            .filter(Column("wordBoxableType") == WordBoxableType.card.rawValue)
    }

    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

struct WordBox: Codable {
    var id: Int64?
    var wordBoxableID: Int64
    var wordBoxableType: WordBoxableType
    var pronouns: [Pronoun]
    var menukad: String
    var transcription: String
    
    var wrappedPronounsHebrew: [String] {
        return pronouns.map { $0.description }
    }
    
    var wrappedPronounsTranscription: [String] {
        return pronouns.map { $0.caseName }
    }
}

extension WordBox: FetchableRecord, MutablePersistableRecord {
    static var databaseTableName: String {
        return "wordBox"
    }
    
    enum Columns: String, ColumnExpression {
        case id, wordBoxableID, wordBoxableType, pronouns, menukad, transcription
    }
    
    static let translations = hasMany(Translation.self, using: ForeignKey(["wordBoxID"], to: ["id"]))
    var translations: QueryInterfaceRequest<Translation> {
        return request(for: WordBox.translations)
    }
    
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}
2

I found the answer to my first question here.

My issue was that my associated data was fetched with the name wordBox
but GRDB was looking for fetched data named header because it is the name of the variable I defined in my CardWithHeader struct.

To fix this, I had to change the name of the array of associated data that was fetched from wordBox to header which I did like so, by adding the forKey method to set the name of the fetched data to header:

var request = Card
    .including(required: Card.header.forKey("header"))
    .asRequest(of: CardWithHeader.self)
    .order(Card.Columns.id.desc)
    .limit(limit, offset: offset)

This now gives me clues to solve my second question.
I will post here if I manage to solve it.

3

Hello @leonardkrief,

I'm happy you found the answer to your first question. Indeed the association keys mush match the decoding keys (properties of the decoded structs).

Yes, it is a valid use case.

You'll write a GRDB request with a lot of including, and a matching Swift struct.

My advice is to proceed one association after the other, because getting everything right on the first attempt can be difficult, especially when some values are contextually renamed ("wordBox" -> "header").

You'll get some hint in The Structure of a Joined Request and the subsequent chapters.

Finally, when I read:

Card.header.forKey("header")

I suggest to move this forKey back to the definition of the Card.header association, as below. This will simplify your requests:

static let header = hasOne(WordBox.self ...).forKey("header")
1 Like
4

Thank you for your answer, I solved my second question too, and I followed your advice to put the forKey directly in my associations :slight_smile:

For anyone that would have a similar issue, here is my final request. I precise that I followed your advise for all my associations and moved all my forKey into their respective associations:

var request = Card
    .including(required: Card.header
        .including(required: WordBox.translation)
    )
    .including(all: Card.subcards
        .including(all: SubCard.content
            .including(required: WordBox.translation)
        )
    )

And here are my structures in which I store the fetched data:

struct CardInfo: FetchableRecord, Decodable {
    var card: Card
    var header: WordBoxInfo
    var subcards: [SubCardInfo]
}

struct SubCardInfo: FetchableRecord, Decodable {
    var title: String
    var content: [WordBoxInfo]
}

struct WordBoxInfo: FetchableRecord, Decodable {
    var pronouns: [String]
    var menukad: String
    var transcription: String
    var translation: Translation
}

The inits work perfectly because the fetched rows have the exact same names as the variables, and the nested inits are automatically setup by the framework for nested includings which is very convenient :slight_smile:

1 Like