Rails 3, custom raw SQL insert statement

Question

I have an Evaluation model. Evaluation has many scores. Whenever a new evaluation is created, a score record is created for each user that needs an evaluation (see below for the current method I am using to do this.) So, for example, 40 score records might be created at once. The Evaluation owner then updates each score record with the User's score.

I'm looking to use raw SQL because each insert is its own transaction and is slow.

I would like to convert the following into a mass insert statement using raw SQL:

def build_evaluation_score_items
  self.job.active_employees.each do |employee|
    employee_score = self.scores.build
    employee_score.user_id = employee.id
    employee_score.save
  end
end

Any thoughts on how this can be done? I've tried adapting a code sample from Chris Heald's Coffee Powered site but, no dice.

Thanks to anyone willing to help!

EDIT 1

I neglected to mention the current method is wrapped in a transaction.

So, essentially, I am trying to add this to the code block so everything is inserted in one statement (** This code snippit is from Chris Heald's Coffee Powered site that discussed the topic. I would ask the question there but the post is > 3 yrs old.):

inserts = []
TIMES.times do
  inserts.push "(3.0, '2009-01-23 20:21:13', 2, 1)"
end
sql = "INSERT INTO user_node_scores (`score`, `updated_at`, `node_id`, `user_id`)VALUES #{inserts.join(", ")}"

I'd be happy to show the code from some of my attempts that do not work...

Thanks again!

Well, I've cobbled together something that resembles the code above but I get a SQL statement invalid error around the ('evaluation_id' portion. Any thoughts?

def build_evaluation_score_items
  inserts = []
  self.job.active_employees.each do |employee|
    inserts.push "(#{self.id}, #{employee.id}, #{Time.now}, #{Time.now})"
  end
  sql = "INSERT INTO scores ('evaluation_id', `user_id`, 'created_at', `updated_at`)VALUES #{inserts.join(", ")}"
  ActiveRecord::Base.connection.execute(sql) 
end

Any idea as to what in the above SQL code is causing the error?

Answer 1

Well, after much trial and error, here is the final answer. The cool thing is that all the records are inserted via one statement. Of course, validations are skipped (so this will not be appropriate if you require model validations on create) but in my case, that's not necessary because all I'm doing is setting up the score record for each employee's evaluation. Of course, validations work as expected when the job leader updates the employee's evaluation score.

def build_evaluation_score_items
  inserts = []
  time = Time.now.to_s(:db)
  self.job.active_employees.each do |employee|
    inserts.push "(#{self.id}, #{employee.id}, '#{time}')"
  end
  sql = "INSERT INTO scores (evaluation_id, user_id, created_at) VALUES #{inserts.join(", ")}"
  ActiveRecord::Base.connection.execute(sql) 
end

Answer 2

Rather than building SQL directly (and opening yourself to SQL injection and other issues), I would recommend the activerecord-import gem. It can issue multi-row INSERT commands, among other strategies.

You could then write something like:

def build_evaluation_score_items
  new_scores = job.active_employees.map do |employee|
    scores.build(:user_id => employee.id)
  end
  Score.import new_scores
end

Answer 3

I think what you're looking for is:

def build_evaluation_score_items
  ActiveRecord::Base.transaction do
    self.job.active_employees.each do |employee|
      employee_score = self.scores.build
      employee_score.user_id = employee.id
      employee_score.save
    end
  end
end

All child transactions are automatically "pushed" up to the parent transaction. This will prevent the overhead of so many transactions and should increase performance.

You can read more about ActiveRecord transactions here.

UPDATE

Sorry, I misunderstood. Keeping the above answer for posterity. Try this:

def build_evaluation_score_items
  raw_sql = "INSERT INTO your_table ('user_id', 'something_else') VALUES "
  insert_values = "('%s', '%s'),"
  self.job.active_employees.each do |employee|
    raw_sql += insert_values % employee.id, "something else"
  end
  ActiveRecord::Base.connection.execute raw_sql
end