Topic: Refactor to prevent an SQL query per record that needs updating

  # This generates an SQL update per energysheet (use with care)
  def self.update_sec_for(energysheets)
    for energysheet in energysheets
        energysheet.cached_sec = -1
        energysheet.save!
    end
  end

Since each record is being updated to the same value is there a better way to do this in Rails?
I guess a single SQL statement would do the trick... I know of find_by_sql but nothing to execute an update SQL query.

Many thanks, AFM!

Re: Refactor to prevent an SQL query per record that needs updating

Off the top of my (limited) head, something like.

def self.update_sec_for(energysheets)
  array = []
  energysheets.each do |sheet|
    array << sheet.id
  end
  self.connection.execute("update energysheets set cached_sec = -1 where id in (#{array})")         
end

Basically you want to access the connection directly. http://api.rubyonrails.org/classes/Acti … ments.html

Last edited by RurouniJones (2007-05-22 09:05:44)

Re: Refactor to prevent an SQL query per record that needs updating

Many many Thanks!

If I can run any kind of SQL using connection.execute that answers everything, and I like you way of collecting the id's smile

Re: Refactor to prevent an SQL query per record that needs updating

def self.update_sec_for(energysheets)
    array = ""
    energysheets.each do |sheet|
      array = array + sheet.id.to_s + ","
    end
    array.chop! # remove last comma
    self.connection.execute("update energysheets set cached_sec = -1 where id in (#{array})")           
  end

Just needed comma's between the id's...

Cheers smile