Grdb.swift: Association implementation help required

[dev-dubaisoftwaresolutions]

What did you do?

I have a model Nutrition as following which is parsed from Remote API that sends array of Nutrition

struct Nutrition: Codable,FetchableRecord, MutablePersistableRecord{
    let id: Int
    let title:String?
    let description: String?
    let mediaImage: String?
    let mediaVideo: String?
    var week: Int
    var day: Int
    let ingredients: [Ingredient]?
}

And following is the Ingredeint model:

struct Ingredient: Codable ,FetchableRecord, MutablePersistableRecord{
    let id:Int
    let ingredientName:String?
    let unit:String?
    let fats:Double?
    let quantity:Double?
    let calories:Double?
    let protein: Double?
}

I want to persist them. As per the business requirement, A record of Nutrition can have multiple Ingredents. Following is the picture of how nutritions JSON looks like:

I created three tables: Nutrition, Ingredient and Nutrient_Ingredient

Nutrient table is as following:

CREATE TABLE "Nutrition" (
    "id"    INTEGER NOT NULL,
    "title" VARCHAR,
    "description"   VARCHAR,
    "mediaImage"    VARCHAR,
    "mediaVideo"    VARCHAR,
    "week"  INTEGER NOT NULL,
    "day"   INTEGER NOT NULL,
    PRIMARY KEY("id")
);

Ingredient Table as follows:

CREATE TABLE "Ingredient" (
    "ingredientName"    VARCHAR,
    "id"    INTEGER NOT NULL,
    "unit"  TEXT,
    "fats"  DOUBLE,
    "calories"  DOUBLE,
    "protein"   DOUBLE,
    "quantity"  DOUBLE,
    PRIMARY KEY("id")
);

and Nutrition_Ingredeint table as following:

CREATE TABLE "Nutrient_Ingredient" (
    "nutrientId"    INTEGER,
    "ingredeintId"  INTEGER,
    FOREIGN KEY("nutrientId") REFERENCES "Nutrition"("id"),
    PRIMARY KEY("nutrientId","ingredeintId"),
    FOREIGN KEY("ingredeintId") REFERENCES "Ingredient"("id")
);

When I get response from API and try to save Nutritions records, I get SQL Error: SQLite error 1 with statementINSERT INTO "nutrition" ("id", "title", "description", "mediaImage", "mediaVideo", "week", "day", "ingredients") VALUES (?,?,?,?,?,?,?,?): table nutrition has no column named ingredients

Error is correct as I don't have any ingredients column in my table but how can I solve it?

What did you expect to happen?

As I want the 'ingredients' property to be ignored while saving it.

What happened instead?

It gave error that the table nutrition doesn't have the column ingredients

Environment

GRDB flavor(s): GRDB
GRDB version: 4.0.1
Installation method: CocoaPods
Xcode version: 10.2.1
Swift version: 5
Platform(s) running GRDB: iOS
macOS version running Xcode:10.15 Beta

[groue]

Hello @dev-dubaisoftwaresolutions,

Your Nutrition type is an improper fit for the PersistableRecord protocol.

Why? Because it has an ingredients property. But PersistableProtocol should only be adopted by types that deal with ONLY ONE table.

As soon as you have a type that wants to deal with multiple tables, as your Nutrition model, don't make it conform to PersistableRecord. Instead, refactor your model types until you fit this rule.

This could give:

// Deals with the Nutrition table
struct Nutrition: Codable, FetchableRecord, PersistableRecord{
    let id: Int
    let title:String?
    let description: String?
    let mediaImage: String?
    let mediaVideo: String?
    var week: Int
    var day: Int
    // IMPORTANT: no ingredients property, because they do not belong to the Nutrition table
}

// Deals with the Ingredient table
struct Ingredient: Codable, FetchableRecord, PersistableRecord{
    let id:Int
    let ingredientName:String?
    let unit:String?
    let fats:Double?
    let quantity:Double?
    let calories:Double?
    let protein: Double?
}

// Decoded from API. Does not deal with database
struct ServerNutrition: Decodable {
    let id: Int
    let title:String?
    let description: String?
    let mediaImage: String?
    let mediaVideo: String?
    var week: Int
    var day: Int
    let ingredients: [Ingredient]?
}

extension Nutrition {
    // Converts ServerNutrition to Nutrition
    init(_ serverNutrition: ServerNutrition) {
        self.init(
            id: serverNutrition.id,
            title: serverNutrition.title,
            description: serverNutrition.description,
            mediaImage: serverNutrition.mediaImage,
            mediaVideo: serverNutrition.mediaVideo,
            week: serverNutrition.week,
            day: serverNutrition.day)
    }
}

// Insert ServerNutrition in the database
func insert(_ db: Database, serverNutrition: ServerNutrition) throws {
    let nutrition = Nutrition(serverNutrition)
    try nutrition.insert(db)

    for ingredient in serverNutrition.ingredient {
        try ingredient.insert(db)
    }
}

I suppose the sample code has to be completed with updates and deletion: you will complete it and adapt it to your needs and style.

For a more guidance, please refer to Good Practices for Designing Record Types. If you still have questions after you have read this guide, please open a new issue.

[groue]

As I want the 'ingredients' property to be ignored while saving it.

I missed this sentence in the long text of your issue ;-)

See #290 and particularly this comment.

[dev-dubaisoftwaresolutions]

That was very comprehensive and helpful. Thanks for the guidance. It certainly helped. One question, is my approach correct if I want to keep track of which ingredients belong to which nutrition? As you might have noticed, I have created another table: Nutrient_Ingredient for the purpose.

[groue]

I want to keep track of which ingredients belong to which nutrition

You have defined a proper many-to-many relationship. Did you learn about GRDB associations? They definitely can help.