[ruby-on-rails] How to execute a raw update sql with dynamic binding in rails

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?

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.

Why use raw SQL for this?

If you have a model for it use where:

f1 = 'foo'
f2 = 'bar'
f3 = 'buzz'
YourModel.where('f1 = ? and f2 = ?', f1, f2).each do |ym|
  # or where(f1: f1, f2: f2).each do (...)
  ym.update(f3: f3) 
end

If you don't have a model for it (just the table), you can create a file and model that will inherit from ActiveRecord::Base

class YourTable < ActiveRecord::Base
  self.table_name = 'your_table' # specify explicitly if needed
end

and again use where the same as above:

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)

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.

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.

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.

In Rails 3.1, you should use the query interface:

• new(attributes)
• create(attributes)
• create!(attributes)
• find(id_or_array)
• destroy(id_or_array)
• destroy_all
• delete(id_or_array)
• delete_all
• update(ids, updates)
• update_all(updates)
• exists?

update and update_all are the operation you need.

See details here: http://m.onkey.org/active-record-query-interface