更新HasMany关联失败Gorm

huangapple
go评论117阅读模式
英文:

Update HasMany Association failing Gorm

问题

当我尝试使用获取的数据更新Shoppinglist结构时，出现了"there is no unique or exclusion constraint matching the ON CONFLICT specification (SQLSTATE 42P10)"错误。

这是我的结构体：

  1. type Shoppinglist struct {
  2. 	Model
  4. 	ID           int            `gorm:"primaryKey" json:"id"`
  5. 	Title        string         `json:"title"`
  6. 	Items        []Item         `json:"items" gorm:"foreignKey:ParentListID;references:ID;"`
  7. 	Owner        string         `json:"owner"`
  8. 	Participants pq.StringArray `gorm:"type:text[]" json:"participants"`
  9. }
  11. type Item struct {
  12. 	Model
  14. 	ParentListID int    `gorm:"primaryKey" json:"parentListId"`
  15. 	Title        string `json:"title"`
  16. 	Position     int    `json:"position"`
  17. 	Bought       bool   `json:"bought"`
  18. }

这是我在尝试编辑列表时执行的代码：

  1. func EditList(id int, data map[string]interface{}) error {
  2. 	//https://github.com/go-gorm/gorm/issues/3487
  4. 	shoppinglist := Shoppinglist{
  5. 		ID:           data["id"].(int),
  6. 		Title:        data["title"].(string),
  7. 		Items:        data["items"].([]Item),
  8. 		Owner:        data["owner"].(string),
  9. 		Participants: data["participants"].([]string),
  10. 	}
  12. 	if err := db.Session(&gorm.Session{FullSaveAssociations: true}).Where("id = ?", id).Updates(&shoppinglist).Error; err != nil {
  13. 		return err
  14. 	}
  16. 	return nil
  17. }

这是我执行EditList并设置所有值传递给map的地方：

  1. type Shoppinglist struct {
  2. 	ID           int
  3. 	Title        string
  4. 	Items        []models.Item
  5. 	Owner        string
  6. 	Participants []string
  7. 	PageNum      int
  8. 	PageSize     int
  9. }
  11. func (s *Shoppinglist) Edit() error {
  12. 	shoppinglist := map[string]interface{}{
  13. 		"id":           s.ID,
  14. 		"title":        s.Title,
  15. 		"items":        s.Items,
  16. 		"owner":        s.Owner,
  17. 		"participants": s.Participants,
  18. 	}
  20. 	return models.EditList(s.ID, shoppinglist)
  21. }

之前我只使用了[]string而不是[]Item，那时一切都更新得很好。现在除了[]Item之外，其他都更新了。

这是执行的SQL查询：

  1. UPDATE "shoppinglists" SET "modified_on"=1628251977096,"title"='kjhdsfgnb',"owner"='janburzinski1@gmail.com',"participants"='{}' WHERE id = 517687 AND "id" = 517687
  3. INSERT INTO "items" ("created_on","modified_on","deleted_at","title","position","bought","parent_list_id") VALUES (1628251977,1628251977116,NULL,'dfkjhgndfjkg',1,false,517687),(1628251977,1628251977116,NULL,'dfgh123',2,true,517687) ON CONFLICT ("parent_list_id") DO UPDATE SET "created_on"="excluded"."created_on","modified_on"="excluded"."modified_on","deleted_at"="excluded"."deleted_at","title"="excluded"."title","position"="excluded"."position","bought"="excluded"."bought" RETURNING "parent_list_id"

我真的想知道如何在Gorm中更新关系，或者为什么这不起作用，因为我已经在Github和Stackoverflow上查看了所有关联问题，但没有找到适合我的答案。

英文:

When I try to update the Shoppinglist struct with the data I get an "there is no unique or exclusion constraint matching the ON CONFLICT specification (SQLSTATE 42P10)" Error

These are my Structs

  1. type Shoppinglist struct {
  2. 	Model
  4. 	ID           int            `gorm:"primaryKey" json:"id"`
  5. 	Title        string         `json:"title"`
  6. 	Items        []Item         `json:"items" gorm:"foreignKey:ParentListID;references:ID;"`
  7. 	Owner        string         `json:"owner"`
  8. 	Participants pq.StringArray `gorm:"type:text[]" json:"participants"`
  9. }
  11. type Item struct {
  12. 	Model
  14. 	ParentListID int    `gorm:"primaryKey" json:"parentListId"`
  15. 	Title        string `json:"title"`
  16. 	Position     int    `json:"position"`
  17. 	Bought       bool   `json:"bought"`
  18. }

And this is the Code I execute when trying to edit a list

  1. func EditList(id int, data map[string]interface{}) error {
  2. 	//https://github.com/go-gorm/gorm/issues/3487
  4. 	shoppinglist := Shoppinglist{
  5. 		ID:           data["id"].(int),
  6. 		Title:        data["title"].(string),
  7. 		Items:        data["items"].([]Item),
  8. 		Owner:        data["owner"].(string),
  9. 		Participants: data["participants"].([]string),
  10. 	}
  12. 	if err := db.Session(&gorm.Session{FullSaveAssociations: true}).Where("id = ?", id).Updates(&shoppinglist).Error; err != nil {
  13. 		return err
  14. 	}
  16. 	return nil
  17. }

This is where I execute the EditList and where I set all the values to pass nito the map:

  1. type Shoppinglist struct {
  2. 	ID           int
  3. 	Title        string
  4. 	Items        []models.Item
  5. 	Owner        string
  6. 	Participants []string
  7. 	PageNum      int
  8. 	PageSize     int
  9. }
  11. func (s *Shoppinglist) Edit() error {
  12. 	shoppinglist := map[string]interface{}{
  13. 		"id":           s.ID,
  14. 		"title":        s.Title,
  15. 		"items":        s.Items,
  16. 		"owner":        s.Owner,
  17. 		"participants": s.Participants,
  18. 	}
  20. 	return models.EditList(s.ID, shoppinglist)
  21. }

Before I was just using a []string instead of []Item and that was working perfectly. Now everything updates except for the []Item

These are the SQL Queries executed:

  1. UPDATE "shoppinglists" SET "modified_on"=1628251977096,"title"='kjhdsfgnb',"owner"='janburzinski1@gmail.com',"participants"='{}' WHERE id = 517687 AND "id" = 517687
  3. INSERT INTO "items" ("created_on","modified_on","deleted_at","title","position","bought","parent_list_id") VALUES (1628251977,1628251977116,NULL,'dfkjhgndfjkg',1,false,517687),(1628251977,1628251977116,NULL,'dfgh123',2,true,517687) ON CONFLICT ("parent_list_id") DO UPDATE SET "created_on"="excluded"."created_on","modified_on"="excluded"."modified_on","deleted_at"="excluded"."deleted_at","title"="excluded"."title","position"="excluded"."position","bought"="excluded"."bought" RETURNING "parent_list_id"

I would really like to know how to Update a Relation in Gorm or why this isn't working because I've been looking through all the Association Issues on Github and Stackoverflow and didn't find a answer that worked for me.

答案1

得分: 0

我在这里看到的第一个问题是，你的Item没有ID，而是使用ParentListID作为主键。这意味着你每个父级只能有一个Item，这违背了使用数组的目的。

为Item创建一个ID字段（用作主键），如果你的方法仍然存在问题，请更新问题。

PS：本来应该在评论中留下这个建议，但是无法评论。

英文:

The first problem I see here is that your Item has no ID but uses the ParentListID as primary key. That means you can only have one Item for each parent which defeats the purpose of having an array.

Create an ID field (used as primary key) for items and if there's still issues with your approach, please update the question.

PS: would have left this in a comment, but can't.

答案2

得分: 0

我只需要在[]Item后面添加*，修复primaryKey的问题并移除引用即可。

  1. type Shoppinglist struct {
  2.     Model
  4.     ID           int            `gorm:"primaryKey" json:"id"`
  5.     Title        string         `json:"title"`
  6.     Items        []*Item        `json:"items" gorm:"foreignKey:ParentListID;"`
  7.     Owner        string         `json:"owner"`
  8.     Participants pq.StringArray `gorm:"type:text[]" json:"participants"`
  9. }
  11. type Item struct {
  12.     Model
  14.     ID           int    `gorm:"primaryKey" json:"id"`
  15.     ParentListID int    `json:"parentListId"`
  16.     ItemID       int    `json:"itemId"`
  17.     Title        string `json:"title"`
  18.     Position     int    `json:"position"`
  19.     Bought       bool   `json:"bought" gorm:"default:false"`
  20. }
英文:

I just needed to add the * to the []Item and fix the problem with the primarykey and remove the reference.

  1. type Shoppinglist struct {
  2. 	Model
  4. 	ID           int            `gorm:"primaryKey" json:"id"`
  5. 	Title        string         `json:"title"`
  6. 	Items        []*Item        `json:"items" gorm:"foreignKey:ParentListID;"`
  7. 	Owner        string         `json:"owner"`
  8. 	Participants pq.StringArray `gorm:"type:text[]" json:"participants"`
  9. }
  11. type Item struct {
  12. 	Model
  14. 	ID           int    `gorm:"primaryKey" json:"id"`
  15. 	ParentListID int    `json:"parentListId"`
  16. 	ItemID       int    `json:"itemId"`
  17. 	Title        string `json:"title"`
  18. 	Position     int    `json:"position"`
  19. 	Bought       bool   `json:"bought" gorm:"default:false"`
  20. }

huangapple
  • 本文由 发表于 2021年8月8日 16:39:11
  • 转载请务必保留本文链接：https://go.coder-hub.com/68699122.html
  • associations
  • go
  • go-gorm
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定