Rails - Feb 2008 - copy 'has_many' objects with one SQL INSERT

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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Feb 9, 2008 9:56 AM, DyingToLearn
<phylae-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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 :)

Mikel

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---