Gorm Upsert with has_many association

GORM Playground Link

https://github.com/go-gorm/playground/pull/459

Description

In this sample, I have a model Article with the field code that is indexed with a unique index. An article has many article names. So I have another model ArticleName with the foreign key to the article table article_id, a field name that is indexed with a unique index (the unique indices in this sample, are useful to perform upsert operations).

I want to ask if it is possible to upsert articles and article names as follows:

// Models

type Article struct {
    ID           uint64
    Code         string `gorm:"index:idx_code,unique"`
    ArticleNames []*ArticleName
}

type ArticleName struct {
    ID        uint64
    ArticleID uint64
    Name      string `gorm:"index:idx_name,unique"`
    Language  string
    Article   *Article
}

// Creation

articles := []*Article{
        {
            Code: "123",
            ArticleNames: []*ArticleName{
                {Name: "Jacket", Language: "en"},
                {Name: "Giacca", Language: "it"},
            },
        },
        {
            Code: "456",
            ArticleNames: []*ArticleName{
                {Name: "Shirt", Language: "en"},
                {Name: "Maglietta", Language: "it"},
            },
        },
    }

DB.Clauses(clause.OnConflict{UpdateAll: true}).
        Session(&gorm.Session{FullSaveAssociations: true}).
        Create(&articles)

// Update (case no changes)

articles = []*Article{
        {
            Code: "123",
            ArticleNames: []*ArticleName{
                {Name: "Jacket", Language: "en"},
                {Name: "Giacca", Language: "it"},
            },
        },
        {
            Code: "456",
            ArticleNames: []*ArticleName{
                {Name: "Shirt", Language: "en"},
                {Name: "Maglietta", Language: "it"},
            },
        },
    }

DB.Clauses(clause.OnConflict{UpdateAll: true}).
        Session(&gorm.Session{FullSaveAssociations: true}).
        Create(&articles)

// Update (case with changes)

articles = []*Article{
        {
            Code: "123",
            ArticleNames: []*ArticleName{
                {Name: "Jacket", Language: "en"},
                {Name: "Giacca", Language: "it"},
            },
        },
        {
            Code: "456bis",
            ArticleNames: []*ArticleName{
                {Name: "T-Shirt", Language: "en"},
                {Name: "Maglietta", Language: "it"},
            },
        },
    }

DB.Clauses(clause.OnConflict{UpdateAll: true}).
        Session(&gorm.Session{FullSaveAssociations: true}).
        Create(&articles)

Now the first DB.Create works and creates the 2 articles with names as expected. 🤟 The second DB.Create returns the following error:

Error 1452: Cannot add or update a child row: a foreign key constraint fails (`gorm`.`article_names`, CONSTRAINT `fk_articles_article_names` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`))
[5.139ms] [rows:0] INSERT INTO `article_names` (`article_id`,`name`,`language`) VALUES (0,'Jacket','en'),(0,'Giacca','it'),(0,'Shirt','en'),(0,'Maglietta','it') ON DUPLICATE KEY UPDATE `article_id`=VALUES(`article_id`),`name`=VALUES(`name`),`language`=VALUES(`language`)

As you can see, the article_id value is set to 0. How to avoid this?

Then the third DB.Create returns the following error:

Error 1452: Cannot add or update a child row: a foreign key constraint fails (`gorm`.`article_names`, CONSTRAINT `fk_articles_article_names` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`))
[8.834ms] [rows:0] INSERT INTO `article_names` (`article_id`,`name`,`language`) VALUES (5,'Jacket','en'),(5,'Giacca','it'),(6,'T-Shirt','en'),(6,'Maglietta','it') ON DUPLICATE KEY UPDATE `article_id`=VALUES(`article_id`),`name`=VALUES(`name`),`language`=VALUES(`language`)

In this case, article_id are set to 4 and 5, but these articles do not exist.

Do you know how to pass the test I wrote in the linked playground?

@jinzhu heeeeeeeeeelppppppp 🙏

Comment From: tommaso1

+1

Comment From: Manik5

+1

Comment From: jinxuanzheng01

+1

Comment From: hewenyu

这个upsert只能根据主键来吧，你的操作对程序来说会新生成一个 主键id，但是由于你设置了唯一字段，无法成功插入，所以中间作为链接的表的外键值也不存在了，你把unique 去掉，理论上应该就行了

This upsert can only be based on the primary key. Your operation will generate a new primary key ID for the program, but because you set a unique field, it cannot be successfully inserted, so the foreign key value of the linked table in the middle does not exist. If you remove unique, it should be OK in theory