I want to execute one update raw sql like below:
update table set f1=? where f2=? and f3=?
This SQL will be executed by ActiveRecord::Base.connection.execute, but I don't know how to pass the dynamic parameter values into the method.
Could someone give me any help on it?
It doesn't look like the Rails API exposes methods to do this generically. You could try accessing the underlying connection and using it's methods, e.g. for MySQL:
st = ActiveRecord::Base.connection.raw_connection.prepare("update table set f1=? where f2=? and f3=?")
st.execute(f1, f2, f3)
st.close
I'm not sure if there are other ramifications to doing this (connections left open, etc). I would trace the Rails code for a normal update to see what it's doing aside from the actual query.
Using prepared queries can save you a small amount of time in the database, but unless you're doing this a million times in a row, you'd probably be better off just building the update with normal Ruby substitution, e.g.
ActiveRecord::Base.connection.execute("update table set f1=#{ActiveRecord::Base.sanitize(f1)}")
or using ActiveRecord like the commenters said.
field=#{value} as this leaves you wide open to SQL injection attacks. If you go down that path, check out the ActiveRecord::ConnectionAdapters::Quoting module. –
Marquess prepare statement in Mysql2 –
Thremmatology execute is dangerous method and can cause memory or other resource leaks. –
Germanium prepare takes at least two arguments: the statement's name and the query. –
Neurosurgery Using prepared queries can save you a small amount of time in the database, ... note that in some databases, namely Oracle, bind variables are absolutely the way to go, and can bring enormous benefits. It's not just about a "small amount of time", but also about saving space, re-using resources, making debugging easier (less stuff to go through in the data dictionary when looking at SQL plans) and others. –
Tavi ActiveRecord::Base.connection has a quote method that takes a string value (and optionally the column object). So you can say this:
ActiveRecord::Base.connection.execute(<<-EOQ)
UPDATE foo
SET bar = #{ActiveRecord::Base.connection.quote(baz)}
EOQ
Note if you're in a Rails migration or an ActiveRecord object you can shorten that to:
connection.execute(<<-EOQ)
UPDATE foo
SET bar = #{connection.quote(baz)}
EOQ
UPDATE: As @kolen points out, you should use exec_update instead. This will handle the quoting for you and also avoid leaking memory. The signature works a bit differently though:
connection.exec_update(<<-EOQ, "SQL", [[nil, baz]])
UPDATE foo
SET bar = $1
EOQ
Here the last param is a array of tuples representing bind parameters. In each tuple, the first entry is the column type and the second is the value. You can give nil for the column type and Rails will usually do the right thing though.
There are also exec_query, exec_insert, and exec_delete, depending on what you need.
execute is dangerous method and can cause memory or other resource leaks. –
Germanium on duplicate key update everywhere –
Charentemaritime ON CONFLICT DO UPDATE if you like. For non-raw SQL this gem looks handy: github.com/jesjos/active_record_upsert –
Cecilia None of the other answers showed me how to use named parameters, so I ended up combining exec_update with sanitize_sql:
User.connection.exec_update(
User.sanitize_sql(
[
"update users set name = :name where id = :id and name <> :name",
{
id: 123,
name: 'My Name'
}
]
)
)
This works for me on Rails 5, and it executes this SQL:
update users set name = 'My Name' where id = 123 and name <> 'My Name'
You need to use an existing Rails model instead of User if you don't have that.
I wanted to use named parameters to avoid issues with the ordering when I use ? or $1/$2,etc. Positional ordering is kind of frustrating when I have more than a handful of parameters, but named parameters allow me to refactor the SQL command without having to update the parameters.
You should just use something like:
YourModel.update_all(
ActiveRecord::Base.send(:sanitize_sql_for_assignment, {:value => "'wow'"})
)
That would do the trick. Using the ActiveRecord::Base#send method to invoke the sanitize_sql_for_assignment makes the Ruby (at least the 1.8.7 version) skip the fact that the sanitize_sql_for_assignment is actually a protected method.
Sometime would be better use name of parent class instead name of table:
# Refers to the current class
self.class.unscoped.where(self.class.primary_key => id).update_all(created _at: timestamp)
For example "Person" base class, subclasses (and database tables) "Client" and "Seller" Instead using:
Client.where(self.class.primary_key => id).update_all(created _at: timestamp)
Seller.where(self.class.primary_key => id).update_all(created _at: timestamp)
You can use object of base class by this way:
person.class.unscoped.where(self.class.primary_key => id).update_all(created _at: timestamp)
Here's a trick I recently worked out for executing raw sql with binds:
binds = SomeRecord.bind(a_string_field: value1, a_date_field: value2) +
SomeOtherRecord.bind(a_numeric_field: value3)
SomeRecord.connection.exec_query <<~SQL, nil, binds
SELECT *
FROM some_records
JOIN some_other_records ON some_other_records.record_id = some_records.id
WHERE some_records.a_string_field = $1
AND some_records.a_date_field < $2
AND some_other_records.a_numeric_field > $3
SQL
where ApplicationRecord defines this:
# Convenient way of building custom sql binds
def self.bind(column_values)
column_values.map do |column_name, value|
[column_for_attribute(column_name), value]
end
end
and that is similar to how AR binds its own queries.
I needed to use raw sql because I failed at getting composite_primary_keys to function with activerecord 2.3.8. So in order to access the sqlserver 2000 table with a composite primary key, raw sql was required.
sql = "update [db].[dbo].[#{Contacts.table_name}] " +
"set [COLUMN] = 0 " +
"where [CLIENT_ID] = '#{contact.CLIENT_ID}' and CONTACT_ID = '#{contact.CONTACT_ID}'"
st = ActiveRecord::Base.connection.raw_connection.prepare(sql)
st.execute
If a better solution is available, please share.
In Rails 3.1, you should use the query interface:
update and update_all are the operation you need.
See details here: http://m.onkey.org/active-record-query-interface
© 2022 - 2024 — McMap. All rights reserved.