英文:
golang gorm upsert with returning
问题
在我的golang项目中，我使用gorm，并且我需要使用返回子句进行upsert查询，以获取查询中修改后的值。我可以进行upsert操作，但是我无法弄清楚如何将返回子句与其连接起来。表名为counters，代码如下：
te := struct {
Name string //key column
Counter int
}{
Name: "name_to_update",
Counter: 2,
}
DB.
//Model(te).
Clauses(
//clause.Returning{Columns: []clause.Column{{Name: "counter"}}},
clause.OnConflict{
Columns: []clause.Column{{Name: "name"}}, // key column
DoUpdates: clause.Assignments(map[string]interface{}{
"counter": gorm.Expr("counters.counter + ?", 1),
}),
},
).Create(&te)
生成的SQL查询语句如下：
INSERT INTO "counters" ("counter", "name") VALUES (0, "name_to_update") ON CONFLICT ("name")
DO UPDATE SET "counter"=counters.counter + 1 RETURNING "name" --我需要更新后的counter值，而不是name
所以计数器已经更新，这是没问题的，但是它返回给我键列（在RETURNING中），而我需要计数器的更新值。有什么办法可以解决吗？谢谢
英文:
In my golang project I use gorm and I need to make upsert query with returning clause to get the modified value from the query. I can make upsert, but I cannot figure out how to connect the returnning clause to it. The table name is counters, the code is as follows:
te := struct {
Name string //key column
Counter int
}{
Name: "name_to_update",
Counter: 2,
}
DB.
//Model(te).
Clauses(
//clause.Returning{Columns: []clause.Column{{Name: "counter"}}},
clause.OnConflict{
Columns: []clause.Column{{Name: "name"}}, // key column
DoUpdates: clause.Assignments(map[string]interface{}{
"counter": gorm.Expr("counters.counter + ?", 1),
}),
},
).Create(&te)
The resulting SQL query is:
INSERT INTO "counters" ("counter", "name") VALUES (0, "name_to_update") ON CONFLICT ("name")
DO UPDATE SET "counter"=counters.counter + 1 RETURNING "name" //I need updated counter value here, not name
So the counter is updated and this is OK, but it returns me the key column (in RETURNING) while I need the updated value of the counter. Any ideas how to fix it? Thank you
答案1
得分: 2
我不确定匿名结构体是否会引发问题。
此外，从你的代码中无法确定表名 "counters" 是从哪里获取的。
我尝试了你的解决方案 - 使用一个专门的结构体来表示模型 - 并且它完全正常工作。
type Counter struct {
Name string `gorm:"primaryKey"`
Counter int
}
...
counter := Counter{Name: "name_to_update", Counter: 2}
db.
Clauses(
clause.Returning{Columns: []clause.Column{{Name: "counter"}}},
clause.OnConflict{
Columns: []clause.Column{{Name: "name"}},
DoUpdates: clause.Assignments(map[string]interface{}{
"counter": gorm.Expr("counters.counter + ?", 1),
}),
},
).Create(&counter)
fmt.Println(counter.Counter)
上述代码生成以下 SQL 语句：
INSERT INTO "counters" ("name","counter") VALUES ('name_to_update',10) ON CONFLICT ("name") DO UPDATE SET "counter"=counters.counter + 1 RETURNING "counter"
而 counter.Counter 的值已经正确更新。
英文:
I'm not sure if the anonymous struct causes an issue.
Also, it's unclear from your code where the table name - "counters" - comes from.
I've tried your solution - but with a dedicated struct for the model - and it works just fine.
type Counter struct {
Name string `gorm:"primaryKey"`
Counter int
}
...
counter := Counter{Name: "name_to_update", Counter: 2}
db.
Clauses(
clause.Returning{Columns: []clause.Column{{Name: "counter"}}},
clause.OnConflict{
Columns: []clause.Column{{Name: "name"}},
DoUpdates: clause.Assignments(map[string]interface{}{
"counter": gorm.Expr("counters.counter + ?", 1),
}),
},
).Create(&counter)
fmt.Println(counter.Counter)
The code above generates the following SQL
INSERT INTO "counters" ("name","counter") VALUES ('name_to_update',10) ON CONFLICT ("name") DO UPDATE SET "counter"=counters.counter + 1 RETURNING "counter"
And counter.Counter has correct updated value.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论