Rails 3、自定义原始SQL插入语句 [英] Rails 3, custom raw SQL insert statement

问题描述

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

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.

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

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:

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

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

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!

编辑 1

我忽略了当前方法被封装在一个事务中.

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

所以，本质上，我试图将其添加到代码块中，以便将所有内容插入到一个语句中(** 该代码片段来自讨论该主题的 Chris Heald 的 Coffee Powered 站点.我会在那里提出问题帖子年龄 > 3 岁.):

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

再次感谢！

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

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

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

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

推荐答案

好吧，经过多次尝试和错误，这里是最终答案.很酷的事情是所有的记录都是通过一个语句插入的.当然，会跳过验证(因此，如果您需要在创建时进行模型验证，这将不合适)，但在我的情况下，这不是必需的，因为我所做的只是为每个员工的评估设置分数记录.当然，当工作负责人更新员工的评估分数时，验证会按预期工作.

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

这篇关于Rails 3、自定义原始SQL插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！