英文:
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)
}
}
现在,让我们仔细看看每个部分。
结构体的定义有一些改变。我添加了关于联接表的定义,并重写了它的列名。
请注意,我只是为了完整性而添加了这个定义,如果你的联接表已经存在于数据库中,你可以忽略它。
另一个改变是如何将数据插入数据库。首先,你需要将数据插入父表(residues
和residue_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!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论