英文:
Get existing and inserted IDs in upsert operation (db.Clauses clause.OnConflict)
问题
我已经翻译了你提供的内容,以下是翻译结果:
我有一个场景,需要将一些数据数组插入到表中。在这种情况下,如果名称和版本的组合已经存在(复合唯一约束),我需要获取这些ID;否则,获取插入的ID。如果两种情况都存在,则获取插入和现有的ID。
下面是我尝试过的模型和代码:
模型依赖
type Dependency struct {
gorm.Model
ID string `gorm:"primaryKey; not null"`
Name string `gorm:"not null; UniqueIndex:idx_name_version"`
Version string `gorm:"not null; UniqueIndex:idx_name_version"`
}
带有gorm查询的go代码来插入依赖项
var saveDependencyData []models.Dependency
// 从API输入中读取依赖项
// [
// {
// "name": "node",
// "version": "16.0.0"
// },
// {
// "name": "node",
// "version": "18.0.0"
// }
// ]
for _, dep := range Dependecies {
saveDependencyData = append(saveDependencyData, models.Dependency{
ID: nanoid.New(),
Name: dep.Name,
Version: dep.Version,
})
}
res := db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "name"}, {Name: "version"}},
DoUpdates: clause.AssignmentColumns([]string{"name"}),
}).Create(saveDependencyData)
gorm查询输出
INSERT INTO "dependencies" ("id","created_at","updated_at","deleted_at","name","version") VALUES ('QanL-nfNFrOGdxG2iXdoQ','2022-10-06 19:21:13.079','2022-10-06 19:21:13.079',NULL,'react','16.0.0'),('Yw1YyQ-aBqrQtwZ72GNtB','2022-10-06 19:21:13.079','2022-10-06 19:21:13.079',NULL,'react','18.0.0') ON CONFLICT ("name","version") DO UPDATE SET "name"="excluded"."name" RETURNING "id"
这个查询返回了我需要的ID列表,但是找不到一种方法来检索它们。
> 使用Scan()会获取该表中的所有数据。
你可以帮助找到一种从上述的GORM db.Clauses()中检索返回的ID的方法,或者提供任何其他优化的方法来获取这些(插入和现有的)ID吗?
英文:
I have a scenario where I need to insert into a table some array of data, In which case if the combination of name and version already exists (composite unique constrain), I need to get those IDs, else get inserted IDs, if both case exist get inserted and existing ids
Models and code I tried are given below:
Model Dependency
type Dependency struct {
gorm.Model
ID string `gorm:"primaryKey; not null"`
Name string `gorm:"not null; UniqueIndex:idx_name_version"`
Version string `gorm:"not null; UniqueIndex:idx_name_version"`
}
go code with gorm query to insert dependencies
var saveDependencyData []models.Dependency
// Dependecies are read form api input
// [
// {
// "name": "node",
// "version": "16.0.0"
// },
// {
// "name": "node",
// "version": "18.0.0"
// }
// ]
for _, dep := range Dependecies {
saveDependencyData = append(saveDependencyData, models.Dependency{
ID: nanoid.New(),
Name: dep.Name,
Version: dep.Version,
})
}
res := db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "name"}, {Name: "version"}},
DoUpdates: clause.AssignmentColumns([]string{"name"}),
}).Create(saveDependencyData)
gorm query output
INSERT INTO "dependencies" ("id","created_at","updated_at","deleted_at","name","version") VALUES ('QanL-nfNFrOGdxG2iXdoQ','2022-10-06 19:21:13.079','2022-10-06 19:21:13.079',NULL,'react','16.0.0'),('Yw1YyQ-aBqrQtwZ72GNtB','2022-10-06 19:21:13.079','2022-10-06 19:21:13.079',NULL,'react','18.0.0') ON CONFLICT ("name","version") DO UPDATE SET "name"="excluded"."name" RETURNING "id"
This query returns the list of ids I needed, but could not find a way to retrieve that.
> using Scan() gets all the datas in that table.
Either you can help with a way to retrieve the returning IDs form the above GORM db.Clauses(), Or any other optimized method to get those (inserted & existing) ids with a upsert query.
答案1
得分: 1
根据注释所示:GORM的几个函数期望参数是指针,并且会使用信息更新变量。
这显然适用于所有主要目的是检索信息的函数(First
、Find
等,参见https://gorm.io/docs/query.html)。
但对于修改数据的函数也是如此,比如
Create
(https://gorm.io/docs/create.html),Update
(https://gorm.io/docs/update.html#Returning-Data-From-Modified-Rows)或Delete
(https://gorm.io/docs/delete.html#Returning-Data-From-Deleted-Rows)。
因此,在这种情况下,解决方案是将Create(&saveDependencyData)
传递给函数,而不是Create(saveDependencyData)
。
在调用之后,与数据库对应的最新信息将在saveDependencyData
中可用。
英文:
As indicated in the comments: Several functions of GORM expect a pointer as argument and will update the variable with information.
That's obviously the case for all functions whose main purpose is to retrieve information (First
, Find
, ..., cf. https://gorm.io/docs/query.html).
But it's also the case for functions that modify data like
Create
(https://gorm.io/docs/create.html),Update
(https://gorm.io/docs/update.html#Returning-Data-From-Modified-Rows) orDelete
(https://gorm.io/docs/delete.html#Returning-Data-From-Deleted-Rows)
So, the solution in this case is to pass Create(&saveDependencyData)
instead of Create(saveDependencyData)
.
The up-to-date information corresponding to the database will then be available in the saveDependencyData
after the call.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论