GORM配置自定义列的多对多关系

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

GORM Configure many to many with customs columns

问题

我有这两个模型:

type Residue struct {
	ID           int
	Name         string

	Categories []*ResidueCategory `gorm:"many2many:residue_residue_categories"`
}

type ResidueCategory struct {
	ID           int
	Name         string

	Residues []*Residue `gorm:"many2many:residue_residue_categories"`
}

我已经有了residue_residue_categories表。但是列是:

  • id(int)
  • residueId(int,外键)
  • residueCategoryId(int,外键)

我想要进行一个追加关联,像这样:

db.Model(&data).Association("Categories").Append(CategoriesData)

但是生成的SQL是:

INSERT INTO "residue_residue_categories" ("residue_id","residue_category_id") VALUES (49,4) ON CONFLICT DO NOTHING

如你所见,插入SQL中的列名不正确。

我已经尝试配置额外的字段,例如:

Categories []*ResidueCategory `gorm:"many2many:residue_residue_categories;foreignkey:residueId;association_foreignkey:residueCategoryId"`
Residues []*Residue `gorm:"many2many:residue_residue_categories;association_foreignkey:residueId;foreignkey:residueCategoryId"`

但是奇怪的是,GORM在简单的GetById(在追加关系之前)中抛出了invalid foreign key: residueId错误。我该如何配置这个关系以及这些自定义列名?

英文:

I have these 2 models:

type Residue struct {
	ID           int
	Name         string

	Categories []*ResidueCategory `gorm:"many2many:residue_residue_categories"`
}

type ResidueCategory struct {
	ID           int
	Name         string

	Residues []*Residue `gorm:"many2many:residue_residue_categories"`
}

I already have the residue_residue_categories table. But the columns are:

  • id (int)
  • residueId (int, foreign key)
  • residueCategoryId (int, foreign key)

And I want to do an append association like:

db.Model(&data).Association("Categories").Append(CategoriesData)

But SQL generate is:

INSERT INTO "residue_residue_categories" ("residue_id","residue_category_id") VALUES (49,4) ON CONFLICT DO NOTHING

As you can see, the columns' names in the insert SQL are not correct.

I've already tried to configure additional fields like:

Categories []*ResidueCategory `gorm:"many2many:residue_residue_categories;foreignkey:residueId;association_foreignkey:residueCategoryId"`
Residues []*Residue `gorm:"many2many:residue_residue_categories;association_foreignkey:residueId;foreignkey:residueCategoryId"`

But weirdly, GORM throws: invalid foreign key: residueId in a simple GetById (before the append relation). How I can configure this relation with these custom columns' names?

答案1

得分: 1

我整理了一个小例子来尝试解决你的问题。首先,让我分享一下代码,然后我会逐步介绍代码中所有相关的部分。

package main

import (
	"fmt"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type Residue struct {
	ID   int
	Name string

	ResidueResidueCategories []*ResidueResidueCategory
}

type ResidueResidueCategory struct {
	ID                int `gorm:"primaryKey"`
	ResidueCategoryId int `gorm:"column:residueCategoryId"`
	ResidueId         int `gorm:"column:residueId"`
}

type ResidueCategory struct {
	ID   int
	Name string

	ResidueResidueCategories []*ResidueResidueCategory
}

func main() {
	dsn := "host=localhost port=54322 user=postgres password=postgres dbname=postgres sslmode=disable"
	db, err := gorm.Open(postgres.Open(dsn))
	if err != nil {
		panic(err)
	}

	db.AutoMigrate(&Residue{}, &ResidueCategory{}, &ResidueResidueCategory{})

	// insert into parent tables
	db.Create(&Residue{ID: 1, Name: "residue 1"})
	db.Create(&ResidueCategory{ID: 1, Name: "category 1"})

	// insert into join table
	db.Debug().Model(&Residue{ID: 1}).Association("ResidueResidueCategories").Append(&ResidueResidueCategory{ResidueCategoryId: 1, ResidueId: 1})

	// fetch data
	var joinTableRecords []ResidueResidueCategory
	if err := db.Model(&ResidueResidueCategory{}).Find(&joinTableRecords).Error; err != nil {
		panic(err)
	}
	for _, v := range joinTableRecords {
		fmt.Println(v)
	}
}

现在,让我们仔细看看每个部分。
结构体的定义有一些改变。我添加了关于联接表的定义,并重写了它的列名。
请注意,我只是为了完整性而添加了这个定义,如果你的联接表已经存在于数据库中,你可以忽略它。

另一个改变是如何将数据插入数据库。首先,你需要将数据插入父表(residuesresidue_categories),然后你可以在residue_residue_categories联接表中添加关联。
我添加了Debug()方法,以便在不附加调试器的情况下显示发送到数据库的SQL语句。

为了更专注于逻辑,我在程序中硬编码了一些值,但是你可以根据自己的逻辑进行调整。

如果这解决了你的问题,请告诉我,谢谢!

英文:

I put together a small example to try to figure out your issue. First, let me share the code, then I'll walk you through all of the relevant sections of the code.

package main

import (
	"fmt"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type Residue struct {
	ID   int
	Name string

	ResidueResidueCategories []*ResidueResidueCategory
}

type ResidueResidueCategory struct {
	ID                int `gorm:"primaryKey"`
	ResidueCategoryId int `gorm:"column:residueCategoryId"`
	ResidueId         int `gorm:"column:residueId"`
}

type ResidueCategory struct {
	ID   int
	Name string

	ResidueResidueCategories []*ResidueResidueCategory
}

func main() {
	dsn := "host=localhost port=54322 user=postgres password=postgres dbname=postgres sslmode=disable"
	db, err := gorm.Open(postgres.Open(dsn))
	if err != nil {
		panic(err)
	}

	db.AutoMigrate(&Residue{}, &ResidueCategory{}, &ResidueResidueCategory{})

	// insert into parent tables
	db.Create(&Residue{ID: 1, Name: "residue 1"})
	db.Create(&ResidueCategory{ID: 1, Name: "category 1"})

	// insert into join table
	db.Debug().Model(&Residue{ID: 1}).Association("ResidueResidueCategories").Append(&ResidueResidueCategory{ResidueCategoryId: 1, ResidueId: 1})

	// fetch data
	var joinTableRecords []ResidueResidueCategory
	if err := db.Model(&ResidueResidueCategory{}).Find(&joinTableRecords).Error; err != nil {
		panic(err)
	}
	for _, v := range joinTableRecords {
		fmt.Println(v)
	}
}

Now, let's take a closer look at each part.
The structs' definitions have been changed a quite. I've to insert the definition for the join table and override its columns names.
> Please note that I added the definition only for completeness, you can ignore it if your join table is already present in the DB.

The other change is how I insert the data into the DB. First, you've to insert the data in the parent tables (residues and residue_categories), then you can add the association within the residue_residue_categories join table.
I added the Debug() method to show you the SQL statement sent to the DB even if you run the code without attaching the debugger.

To barely focus on the logic, I hard-coded some values within the program but it should be easy to adjust to your own logic.
Let me know if this solves your issue, thanks!

huangapple
  • 本文由 发表于 2023年6月20日 22:54:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/76516010.html
匿名

发表评论

匿名网友

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

确定