How to add items into an array that are in the struct but not in the Db table using GRDB

Question

In the My_Quotes table (see MyQuotes struct below) in the Db I am storing the ID of the contact that was selected from the users Contacts on the device. This way if the contacts information is changed the ID remains constant. So I pull the ContactID from the My_Quotes table in the Db and use it to build the ContactFullName, ContactFirst, ContactLast and ContactOrg with func get_ContactName_Org.

The problem I'm having is that ContactFullName, ContactFirst, ContactLast and ContactOrg (see QuoteList struct below) are not in the My_Quotes table. The error is: Fatal error: could not read String from missing column ContactFullName

I understand the error but adding these items into the My_Quotes table causes a host of other problems. How can I insert these items into theArray?

        var theArray = [QuoteList]()

        do {
            try dbQueue_GRDB.read { db in
                for quotes in try QuoteList.fetchAll(db, sql: "SELECT * FROM My_Quotes ORDER BY QuoteID")
                {
                    let fullString = ModelData.get_ContactName_Org(contactID: quotes.ContactID).fullStaring
                    let first = ModelData.get_ContactName_Org(contactID: quotes.ContactID).firstName
                    let last = ModelData.get_ContactName_Org(contactID: quotes.ContactID).lastName
                    let org = ModelData.get_ContactName_Org(contactID: quotes.ContactID).clientOrg

                    theArray.append(QuoteList(QuoteID: quotes.QuoteID,
                                              Quote_Number: quotes.Quote_Number,
                                              Quote_Status: quotes.Quote_Status,
                                              ContactID: quotes.ContactID,
                                              ContactFullName: fullString, // This is not in the My_Quotes table
                                              ContactFirst: first, // This is not in the My_Quotes table
                                              ContactLast: last, // This is not in the My_Quotes table
                                              ContactOrg: org, // This is not in the My_Quotes table
                                              Inv_ID: quotes.Inv_ID,
                                              Locked_Bool: quotes.Locked_Bool))
                }
            }
        } catch {
            print("Couldn't populate the quote list! \(error)")
        }


    static func get_ContactName_Org(contactID: String) -> (firstName: String, lastName: String, clientOrg: String, fullStaring: String)
    {
        let store = CNContactStore()
        var theName = CNContact()
        var theStaring: String = ""
        var firstName: String = ""
        var lastName: String = ""
        var clientOrg: String = ""
        
        do {
            theName = try store.unifiedContact(withIdentifier: contactID, keysToFetch: [CNContactFormatter.descriptorForRequiredKeys(for: .fullName)])
            
        } catch {
            print("Fetching contact data failed: \(error)")
        }
        
        firstName = theName.givenName
        lastName = theName.familyName
        clientOrg = theName.organizationName
        
        theStaring = theName.organizationName == "" ? theName.givenName + " " + theName.familyName : theName.givenName + " " + theName.familyName + ", " + theName.organizationName
        
        return (firstName,lastName,clientOrg,theStaring)
    }
    

struct QuoteList: Codable, FetchableRecord
{
    let QuoteID: Int64
    var Quote_Number: String
    var Quote_Status: String
    var ContactID: String
    var ContactFullName: String // This is not in the My_Quotes table
    var ContactFirst: String // This is not in the My_Quotes table
    var ContactLast: String // This is not in the My_Quotes table
    var ContactOrg: String // This is not in the My_Quotes table
    var Inv_ID: Int64?
    var Locked_Bool: String
}


struct MyQuotes: Codable, FetchableRecord
{
    let QuoteID: Int64
    var Quote_Number: String
    var Quote_Date: String
    var Quote_Status: String
    var NumOf_People: String?
    var Event_Venue: String?
    var Routine_ID: Int64?
    var Routine_Price: Double?
    var Quote_Deposit: Double?
    var Deposit_Date: String?
    var Age_Range: String?
    var Lodging_Exp: Double?
    var Gas_Exp: Double?
    var Meals_Exp: Double?
    var Miscel_Exp: Double?
    var Airfare_Exp: Double?
    var Show_Start: String?
    var Show_End: String?
    var Show_Date: String?
    var Notes_Exp: String?
    var ContactID: String
    var Quote_Rejection: String?
    var Inv_ID: Int64?
    var Quote_Discount: Int?
    var Locked_Bool: String
}

Answer 1

First, do not have QuoteList adopt the FetchableRecord. Why? Because the database rows do not contain enough information to build QuoteList. FetchableRecord comes with an init(row: Row) initializer that is impossible to correctly fulfill, as the fatal error tells you. It is a programmer error to have QuoteList adopt FetchableRecord and feed it with database rows that do not contain enough information.

FetchableRecord is a protocol that comes with handy fetching methods, and that's why you were attracted by it. But now that it is clear that QuoteList needs something else, it is time to fetch differently.

One possible technique is to fetch raw database rows:

// Instead of:
// for quotes in try QuoteList.fetchAll(db, sql: "SELECT * FROM My_Quotes ORDER BY QuoteID")
for rows in try Row.fetchAll(db, sql: "SELECT * FROM My_Quotes ORDER BY QuoteID") {
    let fullString = ModelData.get_ContactName_Org(contactID: quotes.ContactID).fullStaring
    let first = ModelData.get_ContactName_Org(contactID: quotes.ContactID).firstName
    let last = ModelData.get_ContactName_Org(contactID: quotes.ContactID).lastName
    let org = ModelData.get_ContactName_Org(contactID: quotes.ContactID).clientOrg
    
    let quoteList = QuoteList(
        QuoteID: row["QuoteID"],
        Quote_Number: row["Quote_Number"],
        Quote_Status: row["Quote_Status"],
        ContactID: row["ContactID"],
        ContactFullName: fullString,
        ContactFirst: first,
        ContactLast: last,
        ContactOrg: org,
        Inv_ID: row["Inv_ID"],
        Locked_Bool: row["Locked_Bool"])
    
    theArray.append(quoteList)
}

Another technique is to define an actually fetchable model:

struct PartialQuoteList: Codable, FetchableRecord
{
    let QuoteID: Int64
    var Quote_Number: String
    var Quote_Status: String
    var ContactID: String
    var Inv_ID: Int64?
    var Locked_Bool: String
}
for partial in try PartialQuoteList.fetchAll(db, sql: "SELECT * FROM My_Quotes ORDER BY QuoteID") {
    let fullString = ModelData.get_ContactName_Org(contactID: quotes.ContactID).fullStaring
    let first = ModelData.get_ContactName_Org(contactID: quotes.ContactID).firstName
    let last = ModelData.get_ContactName_Org(contactID: quotes.ContactID).lastName
    let org = ModelData.get_ContactName_Org(contactID: quotes.ContactID).clientOrg
    
    let quoteList = QuoteList(
        QuoteID: partial.QuoteID,
        Quote_Number: partial.Quote_Number,
        Quote_Status: partial.Quote_Status,
        ContactID: partial.ContactID,
        ContactFullName: fullString,
        ContactFirst: first,
        ContactLast: last,
        ContactOrg: org,
        Inv_ID: partial.Inv_ID
        Locked_Bool: partial.Locked_Bool)
    
    theArray.append(quoteList)
}