Copy 'has_many' objects with one SQL INSERT

Rails
1

Hi,

I have the following (this has been simplified to make the question at
hand more obvious):
user model has_many favorites
favorite model belongs_to user

After a new user is created, I want to copy all the favorites from an
existing user to the new user. Right now I have the following code:

# bob is the existing user and sam is the new user
bob.favorites.each do | f |
  u = Favorite.new(f.attributes)
  u.user_id = sam.user_id
  u.save!
end

This creates an SQL INSERT for every favorite that bob has.

I know SQL could do this in one command with something like:
INSERT INTO favorites SELECT url, sam.user_id AS user_id FROM
favorites WHERE user_id = bob.user_id

Does ActiveRecord have something in place for me to do this? I would
be nice to keep the code in Ruby rather than SQL.

Thanks in advance.

2

On Feb 9, 2008 9:56 AM, DyingToLearn [email protected] wrote:

After a new user is created, I want to copy all the favorites from an
existing user to the new user. Right now I have the following code:
This creates an SQL INSERT for every favorite that bob has.

Ugh.

I know SQL could do this in one command with something like:
INSERT INTO favorites SELECT url, sam.user_id AS user_id FROM
favorites WHERE user_id = bob.user_id

Yes, that would probably be faster.

Does ActiveRecord have something in place for me to do this? I would
be nice to keep the code in Ruby rather than SQL.

One thing you could try if your DB supports it is wrapping the call in
a transaction. This can help occasionally. I had a nasty two level
looping select/insert statement in Rails, wrapped the thing in a begin
transaction and it went about 20x faster - but this is a bit of a
hack.

But IMHO don’t be afraid to use SQL directly if it is going to handle
the problem in a faster and more powerful manner.

ActiveRecord provides some abstraction from the database. But at the
end of the day, it just runs SQL on the server.

What I do in this situation is make a file in /lib/ and in there
extend ActiveRecord::Base, call the method what you like, and then put
your custom SQL in there. That way you can call it from any model if
you want and when you change databases (as I did one time with this
method) then you know that all your custom SQL is in this one file and
it is easy to change.

That’s what I think anyway :slight_smile:

Mikel