CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3activerecord
Jenny Blunt
Jenny Blunt

Reputation: 1596

Rails3 Raw Sql Insert with Nested Models

What's the best way to deal with a raw sql insert and nested attributes?

My parent model has this:

has_many :kids, :dependent => :destroy
accepts_nested_attributes_for :kids

 def self.sql_insert
   parent_name = []
   kid_name = []
   10.times do
      parent_name.push "('jenny_blunt')"
      kid_name.push "('some_name', '#{parent_id}')"
   end
   sql1 = "INSERT INTO parents (`name`) VALUES #{parent_name.join(", ")}"
   sql2 = "INSERT INTO kids (`name`, `parent_id`) VALUES #{parent_name.join(", ")}"
 ActiveRecord::Base.connection.execute sql1
 ActiveRecord::Base.connection.execute sql2

end

When I run this, parent_id is blank (obviously).

How do I get it to input the parent_id into the child model??

The above is just a test, I know it's not perfect. Any suggestions appreciated.

Upvotes: 0

Views: 1635

Answers (1)

fuzzyalej
fuzzyalej

Reputation: 5973

You should put a RETURNING id in the sql statement, so when you execute the SQL you have an ordered array of hashes of the ids of the inserted queries. More clearly:

   sql1 = "INSERT INTO parents (`name`) VALUES #{parent_name.join(", ")} RETURNING id"
   h_ids = ActiveRecord::Base.connection.execute sql1 #This returns a PGresult[1]
   ids = h_ids.map {|h| h['id']} #Now you have an array of ids

With that array, you will be able to insert the values into the next sql statement

[1] - http://rubydoc.info/gems/pg/0.10.0/PGresult

Upvotes: 1

Related Questions