sql - Rails - 如何在 update_all 查询中正确转义值

Question

所以我有一个 update_all 查询，如下所示:

Task.where(...).update_all('position = position - X, parent_id = Y')

我想用整数值替换 X 和 Y。我想安全地做到这一点:“rails 方式”。
知道我如何实现这一目标吗？

编辑:我的 rails Controller 中没有位置变量。如果 X = 1，则最终查询字面上应包含“position=position-1”。

此外， update_all documentation指定此方法仅采用一个参数:表示 SQL 语句的 SET 部分的字符串、数组或散列。

编辑 2:好的，我通过稍微调整 Arup Rakshit 解决方案让它工作。这是最终的工作解决方案:

Task.update_all(['position = position - ?, parent_id = ?', X, Y])

Answer 1

写成:

Task.where(...)
    .update_all(['position = ?, parent_id = ?', position - X, Y])

阅读 update_all .

conditions - An SQL fragment like "administrator = 1" or [ "user_name = ?", username ]. See conditions in the intro for more info.

演示:

Loading development environment (Rails 4.2.0)
[1] pry(main)> Person.pluck(:name, :email)
   (0.4ms)  SELECT "people"."name", "people"."email" FROM "people"
=> [["xxxx", nil], ["xxxx", nil], ["xxxx", nil]]
[3] pry(main)> Person.where(email: nil).update_all ["name = ?, email = ?", "foo", "test@test.com"]
  SQL (0.7ms)  UPDATE "people" SET name = 'foo', email = 'test@test.com' WHERE "people"."email" IS NULL
=> 3
[4] pry(main)> Person.pluck(:name, :email)
   (0.3ms)  SELECT "people"."name", "people"."email" FROM "people"
=> [["foo", "test@test.com"], ["foo", "test@test.com"], ["foo", "test@test.com"]]
[5] pry(main)>