How to execute a raw update sql with dynamic binding in rails

Question

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

User · Answer

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  lt  lt -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  lt  lt -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  lt  lt -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

User · Answer

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

User · Answer

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

User · Answer

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   gt  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   gt  id  update all created  at  timestamp  Seller where self class primary key   gt  id  update all created  at  timestamp    You can use object of base class by this way   person class unscoped where self class primary key   gt  id  update all created  at  timestamp

User · Answer

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  lt  lt  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  lt   2     AND some other records a numeric field  gt   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

User · Answer

You should just use something like   YourModel update all    ActiveRecord  Base send  sanitize sql for assignment    value   gt    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

User · Answer

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  lt  ActiveRecord  Base   self table name    your table    specify explicitly if needed end   and again use where the same as above

User · Answer

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

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

Examples related to ruby-on-rails

Examples related to activerecord

Examples related to rawsql