Ruby-on-rails Rails 3、自定义原始SQL插入语句

Rails 3, custom raw SQL insert statement

提问by Angelo Chrysoulakis

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.

我有一个评估模型。评价有很多分数。每当创建新的评估时，都会为需要评估的每个用户创建一个分数记录（请参阅下面我用于执行此操作的当前方法。）因此，例如，可能一次创建 40 个分数记录。评估所有者然后用用户的分数更新每个分数记录。

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

我希望使用原始 SQL，因为每个插入都是它自己的事务并且速度很慢。

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

我想使用原始 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.

关于如何做到这一点的任何想法？我试过改编来自 Chris Heald 的 Coffee Powered 网站的代码示例，但没有骰子。

Thanks to anyone willing to help!

感谢任何愿意提供帮助的人！

EDIT 1

编辑 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.):

因此，本质上，我试图将其添加到代码块中，以便将所有内容插入到一个语句中（** 此代码片段来自 Chris Heald 的 Coffee Powered 网站，该网站讨论了该主题。我会在那里提出问题，但帖子是 > 3岁。）：

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?

好吧，我拼凑了一些类似于上面的代码的东西，但是我在 ('evaluation_id' 部分周围出现了一个 SQL 语句无效错误。有什么想法吗？

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?

知道上面的 SQL 代码是什么导致了错误？

回答by Angelo Chrysoulakis

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

回答by willglynn

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 INSERTcommands, among other strategies.

与其直接构建 SQL（并让自己面临 SQL 注入和其他问题），我建议使用activerecord-import gem。它可以发出多行INSERT命令以及其他策略。

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

回答by Chuck Callebs

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.

您可以在此处阅读有关 ActiveRecord 事务的更多信息。

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