Rails - Dec 2005 - efficient INSERTS

Hi,

I''ve got the following models:

Company
Package

with a habtm relationship.

In my controller, I have a ''dispatch'' action which does the
following

@companies.each do |company|
  @packages.each do |package|
    company.packages.push_with_attributes(package, :sent_on => Time.now)
  end
end

my table ''companies_packages'' therefore lists which company
was sent which
package at what time.

If I''m sending 20 packages to 20 companies though, I generate 400
INSERT
statements. Is there a rails way of doing this more efficiently?

i.e. generating an SQL statement like

INSERT INTO `companies_packages` (`company_id`, `package_id`,
''sent_on'')
VALUES
 (1, 101, 2005-12-15),
 (2, 102, 2005-12-15),
 (3, 103, 2005-12-15),
 (4, 104, 2005-12-15),
 (5, 105, 2005-12-15),
 (6, 106, 2005-12-15),
 (7, 107, 2005-12-15),
 (8, 108, 2005-12-15),
 (9, 109, 2005-12-15),
 (10, 110, 2005-12-15);

Thanks in advance

Adam


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

On Dec 15, 2005, at 9:42 AM, Adam Groves wrote:
> Hi,
>
> I''ve got the following models:
>
> Company
> Package
>
> with a habtm relationship.
>
> In my controller, I have a ''dispatch'' action which does
the following
>
> @companies.each do |company|
>   @packages.each do |package|
>     company.packages.push_with_attributes(package, :sent_on =>  
> Time.now)
>   end
> end
>
> my table ''companies_packages'' therefore lists which
company was
> sent which package at what time.
>
> If I''m sending 20 packages to 20 companies though, I generate 400
> INSERT statements. Is there a rails way of doing this more  
> efficiently?
>
> i.e. generating an SQL statement like
>
> INSERT INTO `companies_packages` (`company_id`, `package_id`,  
> ''sent_on'') VALUES
>  (1, 101, 2005-12-15),
>  (2, 102, 2005-12-15),
>  (3, 103, 2005-12-15),
>  (4, 104, 2005-12-15),
>  (5, 105, 2005-12-15),
>  (6, 106, 2005-12-15),
>  (7, 107, 2005-12-15),
>  (8, 108, 2005-12-15),
>  (9, 109, 2005-12-15),
>  (10, 110, 2005-12-15);
>
> Thanks in advance
>
> Adam
I''ve never seen a rails shortcut for that sort of thing.  It''s
delving in to the realm of optimization and customization where you  
get to tweak SQL queries however you please ;)

Duane Johnson
(canadaduane)

On Thursday 15 December 2005 20:16, Duane Johnson wrote:
> On Dec 15, 2005, at 9:42 AM, Adam Groves wrote:
> > Hi,
> >
> > I''ve got the following models:
> >
> > Company
> > Package
> >
> > with a habtm relationship.
> >
> > In my controller, I have a ''dispatch'' action which
does the following
> >
> > @companies.each do |company|
> >   @packages.each do |package|
> >     company.packages.push_with_attributes(package, :sent_on =>
> > Time.now)
> >   end
> > end
> >
> > my table ''companies_packages'' therefore lists which
company was
> > sent which package at what time.
> >
> > If I''m sending 20 packages to 20 companies though, I generate
400
> > INSERT statements. Is there a rails way of doing this more
> > efficiently?
> >
> > i.e. generating an SQL statement like
> >
> > INSERT INTO `companies_packages` (`company_id`, `package_id`,
> > ''sent_on'') VALUES
> >  (1, 101, 2005-12-15),
> >  (2, 102, 2005-12-15),
> >  (3, 103, 2005-12-15),
> >  (4, 104, 2005-12-15),
> >  (5, 105, 2005-12-15),
> >  (6, 106, 2005-12-15),
> >  (7, 107, 2005-12-15),
> >  (8, 108, 2005-12-15),
> >  (9, 109, 2005-12-15),
> >  (10, 110, 2005-12-15);
> >
> > Thanks in advance
> >
> > Adam
>
> I''ve never seen a rails shortcut for that sort of thing. 
It''s
> delving in to the realm of optimization and customization where you
> get to tweak SQL queries however you please ;)
>
> Duane Johnson
> (canadaduane)
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
One thing that might help (I''m sure it will with PostgreSQL) is to wrap
your
loops in a transaction

ActiveRecord::Base.transaction do 
> > @companies.each do |company|
> >   @packages.each do |package|
> >     company.packages.push_with_attributes(package, :sent_on =>
> > Time.now)
> >   end
> > end
end

Otherwise, each insert will be placed in its own transaction.

Luca