我该如何找回创建ID的批量插入在活动记录列表？ [英] How do I retrieve a list of created IDs for bulk insert in Active Record?

问题描述

我有三个型号：

class Coupon < ActiveRecord::Base
  belongs_to :event
  has_many :coupon_events, :dependent => :destroy
  has_many :events, :through => :coupon_events
end 

class Event < ActiveRecord::Base
  belongs_to :event
  has_many :coupon_events, :dependent => :destroy
  has_many :coupons, :through => :coupon_events
end

class CouponEvent < ActiveRecord::Base
  belongs_to :coupon
  belongs_to :event
end

我通过一个CSV文件中读取创建的优惠券和coupon_events。这是非常低效的，因为记录被创建一次，并导致多个查询每一个包括两个INSERT语句。

I read through a CSV file to create coupons and coupon_events. This is terribly inefficient since the records are created one at a time and result in multiple queries each including the two insert statements.

我想使用一个插入查询是这样的：

I'd like to use a single insert query like this:

coupon_string = " ('abc','AAA'), ('123','BBB')"
Coupon.connection.insert("INSERT INTO coupons (code, name) VALUES"+coupon_string)

然后我需要创建为CouponEvent模型中的第二个插入查询，但我需要返回coupon_ids的列表。是有一个内置的方法检索的ID在插入的时间？

I then need to create the second insert query for the CouponEvent model, but I need a list of the returned coupon_ids. Is there a built in method to retrieve the IDs at the time of the insert?

推荐答案

如果你正在使用MySQL，你是不是在另一个脚本/进程插入更多的行，你可以使用LAST_INSERT_ID插入的第一行的ID（）

If you are using mysql and you are not inserting more rows in another script/process, you can get the id of the first row inserted by using last_insert_id()

    first_id = ActiveRecord::Base.connection.execute("select last_insert_id()").first[0]

然后其他记录的ID顺序产生。

And then the ids of the other records are sequentially generated.

即

    data = %w(one two three)
    to_insert = "('" + data.join("'), ('") + "')"
    Model.connection.insert("INSERT INTO models(name) VALUES #{to_insert}")
    first_id = ActiveRecord::Base.connection.execute("select last_insert_id()").first[0].to_i
    hash = {}
    data.each_with_index {|d, i| hash[first_id + i] = d}

这篇关于我该如何找回创建ID的批量插入在活动记录列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！