So I have an update_all query that looks like this:
Task.where(...).update_all('position = position - X, parent_id = Y')
I want to replace X and Y with integer values. And I'd like to do it safely: "the rails way". Any idea of how I can achieve this ?
EDIT: There is no position variable in my rails controller. If X = 1, The final query should literally contain "position=position-1".
Also, The update_all documentation specifies that this method only takes one argument: A string, array, or hash representing the SET part of an SQL statement.
EDIT 2: Alright, I got it working by slightly tweaking Arup Rakshit solution. Here's the final working solution:
Task.update_all(['position = position - ?, parent_id = ?', X, Y])
Write as :
Task.where(...)
.update_all(['position = ?, parent_id = ?', position - X, Y])
Read update_all
.
conditions - An SQL fragment like "administrator = 1" or [ "user_name = ?", username ]. See conditions in the intro for more info.
Demo:
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", "[email protected]"]
SQL (0.7ms) UPDATE "people" SET name = 'foo', email = '[email protected]' WHERE "people"."email" IS NULL
=> 3
[4] pry(main)> Person.pluck(:name, :email)
(0.3ms) SELECT "people"."name", "people"."email" FROM "people"
=> [["foo", "[email protected]"], ["foo", "[email protected]"], ["foo", "[email protected]"]]
[5] pry(main)>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With