In a list view I want to display data from the listed objects proper and
also data from a to-one associated object. To make it a bit more
concrete, I do a find like this
q=<<END
select p.*, e.lastname, e.firstname
from projects p, employees e
where p.leader_id = e.id
order by e.lastname, e.firstname
END
Project.find_by_sql(q)
The purpose of doing it like this is to get all data in a single select,
instead of wasting n selects on retrieving the employee data. lastname
and firstname are added as extra attributes to Project instances. Thus,
in a view I can access them like this
<% for project in @projects %>
<%= project.lastname %>, <%= project.firstname %>
<% end %>
Which is not very elegant as the view here is coupled to the
implementation detail of how the objects are retrieved from the
database. Instead, I''d rather write
<% for project in @projects %>
<%= project.leader_name %>
<% end %>
But how to implement that method? Here''s a first stab
class Project
def leader_name
lastname + '', '' + firstname
end
end
Now, this method works in the special case that the Project instance has
been created with the additional employee attributes. If not, it fails.
What I really want, but don''t see how to get is, in Pseudo Ruby
class Project
def leader_name
if project_leader_has_been_loaded || !has_project_leader_attribs
project_leader.name
else
lastname + '', '' + firstname
end
end
has_project_leader_attribs can be implemented in terms of
ActiveRecord::Base#attribute_present?. But, for
project_leader_has_been_loaded, how can I find out whether an
associated object has already been created?
Michael
--
Michael Schuerig The more it stays the same,
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org
The less it changes!
http://www.schuerig.de/michael/ --Spinal Tap, The Majesty of Rock
Michael Schuerig wrote:> In a list view I want to display data from the listed objects proper and > also data from a to-one associated object. To make it a bit more > concrete, I do a find like thisA solution is coming in the next Rails release.> q=<<END > select p.*, e.lastname, e.firstname > from projects p, employees e > where p.leader_id = e.id > order by e.lastname, e.firstname > END > > Project.find_by_sql(q)This becomes @projects = Project.find(:all, :include => :leader) <% for project in @projects %> <%= project.leader.name %> <% end %> Best, jeremy
On Tuesday 19 April 2005 01:16, Jeremy Kemper wrote:> Michael Schuerig wrote: > > In a list view I want to display data from the listed objects > > proper and also data from a to-one associated object. To make it a > > bit more concrete, I do a find like this > > A solution is coming in the next Rails release.Great, I''ve reinvented a slightly less round wheel again... :-O Just to make sure I understand you correctly, this is the forthcoming syntax and not supposed to work currently> @projects = Project.find(:all, :include => :leader) > > <% for project in @projects %> > <%= project.leader.name %> > <% end %>It''s nice to see the rate at which Rails improves. Michael -- Michael Schuerig The Fifth Rider of the Apocalypse mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org is a programmer. http://www.schuerig.de/michael/
Michael Schuerig wrote:> On Tuesday 19 April 2005 01:16, Jeremy Kemper wrote: > >>Michael Schuerig wrote: >> >>>In a list view I want to display data from the listed objects >>>proper and also data from a to-one associated object. To make it a >>>bit more concrete, I do a find like this >> >>A solution is coming in the next Rails release. > > > Great, I''ve reinvented a slightly less round wheel again... :-O > > Just to make sure I understand you correctly, this is the forthcoming > syntax and not supposed to work currently > > >> @projects = Project.find(:all, :include => :leader) >> >> <% for project in @projects %> >> <%= project.leader.name %> >> <% end %> > > > It''s nice to see the rate at which Rails improves. > > Michael >This is great for reducing the number of queries sent to the database, and solves a pretty glaring feature-gap for Rails. However, I must note the importance of getting your database into at least 3NF for this (luckily Rails encourages this). The more atomic your tables, the fewer columns returned by the join statement (I''m assuming Rails just selects * on the join table). Just a thought -- for production environments, it might be interesting to have something that will parse out erb templates on the fly and just fetch columns that are used. This will effect object-level caching, and might not improve speed even over caching in the general case, but would provide a long-and-oft-requested feature transparently. If that isn''t the Rails way (automagically giving you just what you want, leveraging introspection), I don''t know what is. Sam
Sorry to hijack this thread with a related question: I''m pretty sure that the last time I looked the new code for :include used left outer joins - a question has just occurred to me: doesn''t that have the potential to break pagination (which relies on LIMIT)? Trevor On 18-Apr-05, at 5:09 PM, Sam Goldman wrote:> Michael Schuerig wrote: >> On Tuesday 19 April 2005 01:16, Jeremy Kemper wrote: >>> Michael Schuerig wrote: >>> >>>> In a list view I want to display data from the listed objects >>>> proper and also data from a to-one associated object. To make it a >>>> bit more concrete, I do a find like this >>> >>> A solution is coming in the next Rails release. >> Great, I''ve reinvented a slightly less round wheel again... :-O >> Just to make sure I understand you correctly, this is the forthcoming >> syntax and not supposed to work currently >>> @projects = Project.find(:all, :include => :leader) >>> >>> <% for project in @projects %> >>> <%= project.leader.name %> >>> <% end %> >> It''s nice to see the rate at which Rails improves. >> Michael > > This is great for reducing the number of queries sent to the database, > and solves a pretty glaring feature-gap for Rails. However, I must > note the importance of getting your database into at least 3NF for > this (luckily Rails encourages this). The more atomic your tables, the > fewer columns returned by the join statement (I''m assuming Rails just > selects * on the join table). > > Just a thought -- for production environments, it might be interesting > to have something that will parse out erb templates on the fly and > just fetch columns that are used. This will effect object-level > caching, and might not improve speed even over caching in the general > case, but would provide a long-and-oft-requested feature > transparently. If that isn''t the Rails way (automagically giving you > just what you want, leveraging introspection), I don''t know what is. > > Sam > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
I just discovered this while playing around with implementing the new API in an existing page I''ve got. The old query, done with find_by_sql, used limit with left outer joins and worked fine. The new style seems to not allow :limit (I can see why, but MySQL seems to be doing the right thing if you let the limit in anyway) if you use :include options. --Scott On 4/18/05, Trevor Squires <trevor-k8q5a0yEZAgS+FvcfC7Uqw@public.gmane.org> wrote:> Sorry to hijack this thread with a related question: > > I''m pretty sure that the last time I looked the new code for :include > used left outer joins - a question has just occurred to me: doesn''t > that have the potential to break pagination (which relies on LIMIT)? > > Trevor > > On 18-Apr-05, at 5:09 PM, Sam Goldman wrote: > > > Michael Schuerig wrote: > >> On Tuesday 19 April 2005 01:16, Jeremy Kemper wrote: > >>> Michael Schuerig wrote: > >>> > >>>> In a list view I want to display data from the listed objects > >>>> proper and also data from a to-one associated object. To make it a > >>>> bit more concrete, I do a find like this > >>> > >>> A solution is coming in the next Rails release. > >> Great, I''ve reinvented a slightly less round wheel again... :-O > >> Just to make sure I understand you correctly, this is the forthcoming > >> syntax and not supposed to work currently > >>> @projects = Project.find(:all, :include => :leader) > >>> > >>> <% for project in @projects %> > >>> <%= project.leader.name %> > >>> <% end %> > >> It''s nice to see the rate at which Rails improves. > >> Michael > > > > This is great for reducing the number of queries sent to the database, > > and solves a pretty glaring feature-gap for Rails. However, I must > > note the importance of getting your database into at least 3NF for > > this (luckily Rails encourages this). The more atomic your tables, the > > fewer columns returned by the join statement (I''m assuming Rails just > > selects * on the join table). > > > > Just a thought -- for production environments, it might be interesting > > to have something that will parse out erb templates on the fly and > > just fetch columns that are used. This will effect object-level > > caching, and might not improve speed even over caching in the general > > case, but would provide a long-and-oft-requested feature > > transparently. If that isn''t the Rails way (automagically giving you > > just what you want, leveraging introspection), I don''t know what is. > > > > Sam > > _______________________________________________ > > 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 >-- --Scott
It seems like that would only cause problems for has_many and has_and_belongs_to_many associations of a model. I''m sure someone could patch it to allow limit queries when eagerly loading has_one and belongs_to associations only. But, would this cause a lot of confusion with the new API? On 4/19/05, Scott Hill <stmpjmpr-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I just discovered this while playing around with implementing the new > API in an existing page I''ve got. The old query, done with > find_by_sql, used limit with left outer joins and worked fine. The new > style seems to not allow :limit (I can see why, but MySQL seems to be > doing the right thing if you let the limit in anyway) if you use > :include options. > > --Scott-- rick http://techno-weenie.net
This would be easily solved in rdbms''s that support subqueries. Instead of: select j.* from models j left join products p on p.model_id = j.id we''d use: select j.* from (select * from models limit 10) j left join products p on p.model_id = j.id It seems that even MySQL (since 4.1) supports subqueries nowadays, so it would be possible to support limits even with eager loading with most of the modern databases. //jarkko On 19.4.2005, at 21:16, Rick Olson wrote:> It seems like that would only cause problems for has_many and > has_and_belongs_to_many associations of a model. I''m sure someone > could patch it to allow limit queries when eagerly loading has_one and > belongs_to associations only. But, would this cause a lot of > confusion with the new API? > > On 4/19/05, Scott Hill <stmpjmpr-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> I just discovered this while playing around with implementing the new >> API in an existing page I''ve got. The old query, done with >> find_by_sql, used limit with left outer joins and worked fine. The new >> style seems to not allow :limit (I can see why, but MySQL seems to be >> doing the right thing if you let the limit in anyway) if you use >> :include options. >> >> --Scott > > > -- > rick > http://techno-weenie.net > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
There is also, I think, a problem with eager loading for has_one associations. Parts of the allowed has_one semantics are dropped. For example, has_one allows the :order option. When used without eager loading, the association returns the correct object (i.e. the first row in an ordered set of corresponding rows). When eager loading is activated, the generated query does not retain this notion of order. Julien -----Original Message----- From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Jarkko Laine Sent: Wednesday, April 20, 2005 9:49 AM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org; Rick Olson Subject: Re: [Rails] Re: List views: avoiding n+1 selects This would be easily solved in rdbms''s that support subqueries. Instead of: select j.* from models j left join products p on p.model_id = j.id we''d use: select j.* from (select * from models limit 10) j left join products p on p.model_id = j.id It seems that even MySQL (since 4.1) supports subqueries nowadays, so it would be possible to support limits even with eager loading with most of the modern databases. //jarkko On 19.4.2005, at 21:16, Rick Olson wrote:> It seems like that would only cause problems for has_many and > has_and_belongs_to_many associations of a model. I''m sure someone > could patch it to allow limit queries when eagerly loading has_one and> belongs_to associations only. But, would this cause a lot of > confusion with the new API? > > On 4/19/05, Scott Hill <stmpjmpr-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> I just discovered this while playing around with implementing the new>> API in an existing page I''ve got. The old query, done with >> find_by_sql, used limit with left outer joins and worked fine. The >> new style seems to not allow :limit (I can see why, but MySQL seems >> to be doing the right thing if you let the limit in anyway) if you >> use :include options. >> >> --Scott > > > -- > rick > http://techno-weenie.net > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net http://odesign.fi
Michael Schuerig wrote:>On Tuesday 19 April 2005 01:16, Jeremy Kemper wrote: > > >>Michael Schuerig wrote: >> >> >>>In a list view I want to display data from the listed objects >>>proper and also data from a to-one associated object. To make it a >>>bit more concrete, I do a find like this >>> >>> >>A solution is coming in the next Rails release. >> >> > >Great, I''ve reinvented a slightly less round wheel again... :-O > >Just to make sure I understand you correctly, this is the forthcoming >syntax and not supposed to work currently > >Can I 2nd that question as I didnt see an answer come through... _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 4/21/05, Sean T Allen <sean-5W9FBhQXBOtBDgjK7y7TUQ@public.gmane.org> wrote:> Michael Schuerig wrote: > On Tuesday 19 April 2005 01:16, Jeremy Kemper wrote: > > > Michael Schuerig wrote: > > > In a list view I want to display data from the listed objects > proper and also data from a to-one associated object. To make it a > bit more concrete, I do a find like this > > A solution is coming in the next Rails release. > > Great, I''ve reinvented a slightly less round wheel again... :-O > > Just to make sure I understand you correctly, this is the forthcoming > syntax and not supposed to work currently > > > Can I 2nd that question as I didnt see an answer come through...No, it should be working now.> > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > > >-- Cheers Koz