Rails:如何增加模型选定实例的整数字段? [英] Rails: How to increment an integer field of selected instances of a model?

问题描述

Buyer 模型有两个字段:

• 名称(字符串)
• 位置(整数)

我想增加position >= N的所有买家的position.

I would like to increment the position of all buyers whose position >= N.

最简单的方法是什么?

是否可以仅使用一个查询来实现这一点?

Is that possible to achieve this using only one query ?

推荐答案

您可以使用:

Buyer.update_all("position = position + 1", ["position >= ?", n])

如果 n = 25，这将生成查询:

This would generate the query, if n = 25:

UPDATE "buyers" SET position = position + 1 WHERE (position >= 25)

因为您有 UNIQUE 数据库约束，所以您有几个选择.对于这两个选项，我建议在事务中运行它们.首先，您可以以相反的顺序单独更新每个字段，但这会导致您有 N+1 次查询.对于小数据集，这不会有问题，但对于较大的数据集，这可能会影响性能.

Being that you have UNIQUE database constraints, you have a couple of options. For both options, I recommend running them in a transaction. First, you can update each field individually in reverse order, but this will cause you to have N+1 queries. For a small dataset, this will not be a problem, but for larger dataset, this could impact performance.

Buyer.transaction do
   Buyer.select("id, position").where(["position >= ?", n]).order("position DESC").each do |buyer|
      buyer.position += 1
      buyer.save
   end
end

为了避免 N+1 查询，另一个选项是将位置增量更改为 100(或 10).这将允许您更新两个查询中的位置，而不是 N+1.因此，您将拥有 100、200、300 等位置，而不是位置 1、2、3 等.然后要进行更新，将所有值都增加 101，然后在更新之后进行更新以减去 1.

The other option, to avoid N+1 queries, is to change the position increments to 100 (or 10). This will allow you to update the positions in two queries, rather than N+1. So instead of having positions 1, 2, 3, etc. you would have 100, 200, 300, etc. Then to do an update, you would increment all values by 101, and then follow update with an update to subtract the 1.

Buyer.transaction do
   Buyer.where(["position >= ?", n]).scoping do
      Buyer.update_all("position = position + 101")
      Buyer.update_all("position = position - 1")
   end
end

这篇关于Rails:如何增加模型选定实例的整数字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！