英文:
Update is ignoring the Limit clause
问题
Gorm似乎不支持在Update中使用Limit，并且也不会抛出错误。
这个查询将更新数据库中所有字段的状态，从VoucherStatusAvailable更新为VoucherStatusBooked，而不考虑Limit的使用。
有人在这里快速解决了这个问题：https://penkovski.com/post/gorm-update-returning/
但是他们将Limit放在Where子句中的解决方案：
resUpdate := tx.Model(&daos.Voucher{}).
Where("status = ? LIMIT ?", models.VoucherStatusAvailable, quantity).
Update("status", models.VoucherStatusBooked)
当使用gorm软删除时，这种方法不起作用，因为它在where子句周围放置了括号，并在其后添加了AND deleted_at = NULL过滤器。
最初我是用两个分开的查询（先查询再更新）来做的，但在这种情况下可能会出现冲突。
有人有什么主意，我应该如何选择表中的X个成员，然后更新它们的状态，而不会与所选项发生冲突吗？
编辑：我使用的DBMS是PostgreSQL。
英文:
Gorm doesn't seem to support the use of Limit with Update, and doesn't throw an error either.
resUpdate := tx.Model(&daos.Voucher{}).
Where("status = ?", models.VoucherStatusAvailable).
Limit(quantity).
Scan(&vouchers).
Update("status", models.VoucherStatusBooked)
This query will update every field in my DB from VoucherStatusAvailable to VoucherStatusBooked, without regards for the use of Limit.
Someone quickly addressed the subject here : https://penkovski.com/post/gorm-update-returning/
But their solution of putting the Limit in the Where clause :
resUpdate := tx.Model(&daos.Voucher{}).
Where("status = ? LIMIT ?", models.VoucherStatusAvailable, quantity).
Update("status", models.VoucherStatusBooked)
doesn't work when using gorm soft delete, since it put parentheses around the where clause, and the AND deleted_at = NULL filter after it.
Initially I was doing it with two separated query (a select then an update) but in this case there is a chance of conflict.
Does anyone have an idea of how I should do the selection of X members of the table, then update their status, without having a risk of conflict with the selected items ?
Edit : The DBMS I'm using is PostgreSQL
答案1
得分: 0
不同的问题得到了回答：
- Postgre不支持LIMIT和ORDER BY，所以我需要一个替代方案。
- 正确的替代方案似乎是CTEs，正如评论中指出的那样。
- gorm支持自定义插件，有一个插件实现了CTE：https://github.com/WinterYukky/gorm-extra-clause-plugin。不幸的是，它与Update子句不兼容。
- 因此，我找到的唯一解决方案是在原始SQL中使用CTE：
var updated []Voucher
resUpdate := tx.Raw(`WITH v AS (
SELECT * from vouchers WHERE status = ?
LIMIT ?
)
UPDATE vouchers SET status = ?
WHERE EXISTS (SELECT * FROM v WHERE vouchers.id = v.id)
RETURNING *`,
models.ArticleStatusAvailable, quantity, models.VoucherStatusBooked).
Scan(&updated)
英文:
Different point were answered :
- Postgre doesn't support LIMIT and ORDER BY, so I needed an alternative.
- The right alternative seemed to be CTEs, as pointed out in a comment.
- gorm support custom plugin, and there is a plugin that implement CTE : https://github.com/WinterYukky/gorm-extra-clause-plugin. Unfortunately, it isn't compatible with the Update clause.
- So ultimately, the only solution I found is to do a CTE in raw SQL :
var updated []Voucher
resUpdate := tx.Raw(`WITH v AS (
SELECT * from vouchers WHERE status = ?
LIMIT ?
)
UPDATE vouchers SET status = ?
WHERE EXISTS (SELECT * FROM v WHERE vouchers.id = v.id)
RETURNING *`,
models.ArticleStatusAvailable, quantity, models.VoucherStatusBooked).
Scan(&updated)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论