在Golang的GORM中,如何引用同一张表的两个列,并且在插入时出现问题?

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

golang gorm reference two columns to same table, problem with insert

问题

原始想法是从Yii中构建一个RBAC类似的模型。您有两个模型:AuthItem和AuthRelations。您已经在auth_items表中有一些数据,并且希望使用GORM将数据插入到auth_relations表中。但是,您遇到了一个错误,它说无法将值设置为Parent字段。您尝试使用了Value()函数,它解决了这个问题,但是约束、外键和引用停止工作。您想知道是否有任何方法可以正确地建立这样的关系,或者如何在不丢失约束的情况下使用value()函数。

请注意,我只提供翻译服务,不回答关于代码的问题。

英文:

original idea was to build rbac analog from yii https://github.com/yiisoft/yii2/blob/master/framework/rbac/migrations/schema-pgsql.sql

so, i have these two models:

type AuthItem struct {
	ID          uint   `gorm:"uniqueIndex;primaryKey;auto_increment;column:id" json:"id"`
	Name        string `gorm:"uniqueIndex;primaryKey;not null;type:varchar(64);column:name" json:"name"`
	ItemType    int64  `gorm:"type:smallint;not null;column:item_type" json:"item_type"`
	Description string `gorm:"size:255;column:description" json:"description"`
}

type AuthRelations struct {
	gorm.Model
	Parent AuthItem `gorm:"references:id;foreignKey:parent;column:parent" json:"parent"`
	Child  AuthItem `gorm:"references:id;foreignKey:child;column:child" json:"child"`
}

also i already have some data in auth_items table and i want to make insert into auth_relations table with GORM, and its looks like this:

var relation = models.AuthRelations{
	Parent: models.AuthItem{ID: 1},
	Child:  models.AuthItem{ID: 2},
}

err = db.Save(&relation).Error
if err != nil {
	log.Fatalf("cant insert: %v", err)
}

i getting this error:

failed to set value 0x1 to field Parent; failed to set value 0x1 to field Parent 

i tried to use gorm function Value(), something like:

func (item AuthItem) Value() (driver.Value, error) {
	return int64(item.ID), nil 
}

and after i implement this function db.Save works, but the constraints/foreignKeys/references stop working

so my question: is there any options to make relations like this in right way or how can i use value() function without loosing constraints ?

答案1

得分: 0

关系更接近于“一对多”或“多对多”,一个父级可以有多个子级。

由于我们引用的是与子级相同的类型,我们可以将模型更新如下:

type AuthItem struct {
    ID          uint   `gorm:"primaryKey; column:id" json:"id"`
    Name        string `gorm:"primaryKey; not null; type:varchar(64); column:name" json:"name"`
    ItemType    int64  `gorm:"type:smallint; not null; column:item_type" json:"item_type"`
    Description string `gorm:"size:255; column:description" json:"description"`

    AuthRelations []AuthItem `gorm:"many2many:auth_relations; foreignKey:ID; joinForeignKey:Parent; References:ID; joinReferences:Child; constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
}

AuthItem可以插入如下:

var relation = []AuthItem{
  {
    ID: 1,
    Name: "super",
    AuthRelations: []AuthItem{
      { ID: 2, Name: "admin" },
      { ID: 3, Name: "owner" },
    },
  }, {
    ID: 2,
    Name: "user",
    AuthRelations: []AuthItem{
      { ID: 3, Name: "normal" },
      { ID: 5, Name: "client" },
    },
  },
}
err = db.Save(&relation).Error
if err != nil {
  log.Fatalf("cant insert: %v", err)
}
log.Printf("Relation: %#v\n", &relation)

可以使用自引用的一对多关系来实现相同的效果,其中我们不需要第二个表,可以使用同一个表并添加一个额外的列作为引用。

type AuthItem struct {
    ID          uint   `gorm:"primaryKey; column:id" json:"id"`
    Name        string `gorm:"primaryKey; not null; type:varchar(64); column:name" json:"name"`
    ItemType    int64  `gorm:"type:smallint; not null; column:item_type" json:"item_type"`
    Description string `gorm:"size:255; column:description" json:"description"`

    Parent *uint `gorm:"column: parent;" json:"parent"`
    AuthRelations []AuthItem `gorm:"foreignKey:Parent"; constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
}

我们可以以与之前相同的方式插入记录。

英文:

The relation is more near to one-to-many or many-to-many has one parent can have multiple children's

Since we are referring to the same type as children we can update the model as below:

type AuthItem struct {
    ID          uint   `gorm:"primaryKey; column:id" json:"id"`
    Name        string `gorm:"primaryKey; not null; type:varchar(64); column:name" json:"name"`
    ItemType    int64  `gorm:"type:smallint; not null; column:item_type" json:"item_type"`
    Description string `gorm:"size:255; column:description" json:"description"`

    AuthRelations []AuthItem `gorm:"many2many:auth_relations; foreignKey:ID; joinForeignKey:Parent; References:ID; joinReferences:Child; constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
}

AuthItem can be inserted as

var relation = []AuthItem{
  {
    ID: 1,
    Name: "super",
    AuthRelations: []AuthItem{
      { ID: 2, Name: "admin" },
      { ID: 3, Name: "owner" },
    },
  }, {
    ID: 2,
    Name: "user",
    AuthRelations: []AuthItem{
      { ID: 3, Name: "normal" },
      { ID: 5, Name: "client" },
    },
  },
}
err = db.Save(&relation).Error
if err != nil {
  log.Fatalf("cant insert: %v", err)
}
log.Printf("Relation: %#v\n", &relation)

Same can be achieved with Self-Referential-Has-Many where we don't require second table and can use the same table with one extra column as reference

type AuthItem struct {
    ID          uint   `gorm:"primaryKey; column:id" json:"id"`
    Name        string `gorm:"primaryKey; not null; type:varchar(64); column:name" json:"name"`
    ItemType    int64  `gorm:"type:smallint; not null; column:item_type" json:"item_type"`
    Description string `gorm:"size:255; column:description" json:"description"`

    Parent *uint `gorm:"column: parent;" json:"parent"`
    AuthRelations []AuthItem `gorm:"foreignKey:Parent"; constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
}

we can insert record the same manner as we did before

答案2

得分: 0

我得出结论,我需要使用多对多关系,并且这里有一个案例示例:

type AuthItem struct {
    ID          uint      `gorm:"uniqueIndex;primaryKey;auto_increment" json:"id"`
    Name        string    `gorm:"unique;not null;type:varchar(64);column:name" json:"name"`
    ItemType    int64     `gorm:"type:smallint;not null;column:item_type" json:"item_type"`
    Description string    `gorm:"size:255;column:description" json:"description"`
    CreatedAt   time.Time `gorm:"default:CURRENT_TIMESTAMP" json:"created_at"`
    UpdatedAt   time.Time `gorm:"default:CURRENT_TIMESTAMP" json:"updated_at"`
    DeletedAt   time.Time `gorm:"default:null" json:"deleted_at"`
}

type AuthRelations struct {
    ID        uint       `gorm:"uniqueIndex;primaryKey;auto_increment" json:"id"`
    Parent    []AuthItem `gorm:"many2many:prnt_authitem_parent;References:Name;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;" json:"parent"`
    Child     []AuthItem `gorm:"many2many:chld_authitem_child;References:Name;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;" json:"child"`
    CreatedAt time.Time  `gorm:"default:CURRENT_TIMESTAMP" json:"created_at"`
    UpdatedAt time.Time  `gorm:"default:CURRENT_TIMESTAMP" json:"updated_at"`
    DeletedAt time.Time  `gorm:"default:null" json:"deleted_at"`
}

希望这能帮到你!

英文:

i came to conclusion that i need to use many2many relation and here is example for case:

type AuthItem struct {
	ID          uint      `gorm:"uniqueIndex;primaryKey;auto_increment" json:"id"`
	Name        string    `gorm:"unique;not null;type:varchar(64);column:name" json:"name"`
	ItemType    int64     `gorm:"type:smallint;not null;column:item_type" json:"item_type"`
	Description string    `gorm:"size:255;column:description" json:"description"`
	CreatedAt   time.Time `gorm:"default:CURRENT_TIMESTAMP" json:"created_at"`
	UpdatedAt   time.Time `gorm:"default:CURRENT_TIMESTAMP" json:"updated_at"`
	DeletedAt   time.Time `gorm:"default:null" json:"deleted_at"`
}

type AuthRelations struct {
	ID        uint       `gorm:"uniqueIndex;primaryKey;auto_increment" json:"id"`
	Parent    []AuthItem `gorm:"many2many:prnt_authitem_parent;References:Name;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;" json:"parent"`
	Child     []AuthItem `gorm:"many2many:chld_authitem_child;References:Name;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;" json:"child"`
	CreatedAt time.Time  `gorm:"default:CURRENT_TIMESTAMP" json:"created_at"`
	UpdatedAt time.Time  `gorm:"default:CURRENT_TIMESTAMP" json:"updated_at"`
	DeletedAt time.Time  `gorm:"default:null" json:"deleted_at"`
}

</details>



huangapple
  • 本文由 发表于 2022年5月11日 11:13:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/72194979.html
匿名

发表评论

匿名网友

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

确定