在upsert操作(db.Clauses clause.OnConflict)中获取已存在和已插入的ID。

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

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的几个函数期望参数是指针,并且会使用信息更新变量。

这显然适用于所有主要目的是检索信息的函数(FirstFind等,参见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

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.

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

发表评论

匿名网友

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

确定