I have found myself frequently writing controller actions that query multiple
tables. But if I just utilize ActiveRecord has_one,has_many,belongs_to, ...
relations alone, I get a whole bunch of little queries (sometimes duplicates)
that substantially detriment performance. So, I end up doings something like
this.
def bind_records(to, from, ref)
hash = {}; to.each { |i| hash[i.id] = i }
from.each { |i| i.send("set_"+ref.to_s+"_target",
hash[i.send(ref.to_s+"_id")]) }
end
@ta = ClassA.find_by_sql(....)
@tb = ClassB.find_by_sql(....)
bind_records(@tb, @tb, :a_ref)
After this bit of kludgyness I can use ActiveRecord relations as expected.
I also sometimes just do one find_by_sql and add columns that don''t
exist in
the underlying database table.
Is this a common problem? Does someone have a better solution?
I noticed
http://wiki.rubyonrails.com/rails/show/ActiveRecord+and+the+Relational+Model+-+alternatives
but this idea doesn''t really seem to address my issue.
--
Quinn Harris
(970) 759-5163
quinn-i1JSih8DuHelVyrhU4qvOw@public.gmane.org
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
On Monday 12 September 2005 20:03, Quinn Harris wrote:> Is this a common problem? Does someone have a better solution?Have a look at the :includes option of ActiveRecord::Base#find Michael -- Michael Schuerig The Fifth Rider of the Apocalypse mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org is a programmer. http://www.schuerig.de/michael/
That is a help, but I also have queries that need to resolve two associations (A JOIN B ON A.ref = B.id JOIN C ON B.ref = C.id). What makes it worse is in one case I order on columns in A and C I also have queries that use GROUP BY and need joins usually resulting in something that still looks like the underlying tables. And finally I have some queries that could return 100''s of rows from one table but only a couple rows from another table the first references. Would this not be better done with two queries? I expect these are unusual for most rails projects, but somehow I managed to devise a schema where one of these things is more common than not. And I don''t see a way to avoid it without dropping functionality. Am I the only one in this boat? Would extending ActiveRecord to handle these cases be useful or feature bloat? But, I can work around it for the time being, so its not critical. On Monday 12 September 2005 12:40 pm, Michael Schuerig wrote:> On Monday 12 September 2005 20:03, Quinn Harris wrote: > > Is this a common problem? Does someone have a better solution? > > Have a look at the :includes option of ActiveRecord::Base#find > > Michael-- Quinn Harris (970) 759-5163 quinn-i1JSih8DuHelVyrhU4qvOw@public.gmane.org _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 12.9.2005, at 23.54, Quinn Harris wrote:> That is a help, but I also have queries that need to resolve two > associations > (A JOIN B ON A.ref = B.id JOIN C ON B.ref = C.id). What makes it > worse is in > one case I order on columns in A and CIf you look at the API docs for ActiveRecord::Base#find [1], you''ll find out that the function has both :order and :joins parameters. You can (and often have to) alias the tables you are using in the join and can use the same aliases in the :order parameter. Person.find(:all, :order => "p.number desc", :joins => "join phone_numbers p on persons.id = p.person_id") There''s no magic in those parameters, they''re just used to build the sql query. Thus, you can use as deep join hierarchies as you want to. Just remember that ActiveRecord doesn''t know about the other objects in this case so there''ll only be Person objects at hand and all the attributes belong to them. //jarkko [1] http://rails.rubyonrails.com/classes/ActiveRecord/Base.html#M000673> > I also have queries that use GROUP BY and need joins usually > resulting in > something that still looks like the underlying tables. > > And finally I have some queries that could return 100''s of rows > from one table > but only a couple rows from another table the first references. > Would this > not be better done with two queries? > > I expect these are unusual for most rails projects, but somehow I > managed to > devise a schema where one of these things is more common than not. > And I > don''t see a way to avoid it without dropping functionality. Am I > the only > one in this boat? Would extending ActiveRecord to handle these > cases be > useful or feature bloat? But, I can work around it for the time > being, so > its not critical. > > > On Monday 12 September 2005 12:40 pm, Michael Schuerig wrote: > >> On Monday 12 September 2005 20:03, Quinn Harris wrote: >> >>> Is this a common problem? Does someone have a better solution? >>> >> >> Have a look at the :includes option of ActiveRecord::Base#find >> >> Michael >> > > -- > Quinn Harris > (970) 759-5163 > quinn-i1JSih8DuHelVyrhU4qvOw@public.gmane.org > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails