jason lynes :: senyl.com
2005-May-06 20:32 UTC
Limiting a find(:all) with eager beaver loading
is there an easy way to limit the number of results returned when eager
loading? here''s my code:
@posts = Article.find(:all,
:conditions => "categories.id = 2 and published=1",
:order => "created_at DESC",
:include => [ :categories, :keywords]
)
thanks
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
You can use :offset and :limit just like other find calls.> @posts = Article.find(:all, > :conditions => "categories.id = 2 and published=1", > :order => "created_at DESC", > :include => [ :categories, :keywords],:offset => 0, :limit => 10> )Duane Johnson (canadaduane) _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 6.5.2005, at 23:32, jason lynes :: senyl.com wrote:> is there an easy way to limit the number of results returned when > eager loading?Short answer: not really. The limiting is done with normal SQL limit clause and it''s not really useful in join queries, because you normally get more than one row per "primary" object and AR then builds the objects from those rows. E.g. in your code below you can''t limit the number of the article objects returned without using subqueries and AR doesn''t currently do that. Besides, I heard that even though MySQL "supports" subqueries nowadays, it does next to none optimization on them so it''s actually a lot faster to use separate queries to get the associated categories and keywords. //jarkko> here''s my code: > > > @posts = Article.find(:all, > :conditions => "categories.id = 2 and published=1", > :order => "created_at DESC", > :include => [ :categories, :keywords] > ) > > > thanks > _______________________________________________ > 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
On 7.5.2005, at 00:14, Duane Johnson wrote:> You can use :offset and :limit just like other find calls. > >> @posts = Article.find(:all, >> :conditions => "categories.id = 2 and published=1", >> :order => "created_at DESC", >> :include => [ :categories, :keywords], > :offset => 0, > :limit => 10 >> )Please don''t! Read my other post for the reason. //jarkko> > Duane Johnson > (canadaduane)_______________________________________________ > 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
Jarkko Laine wrote:> On 6.5.2005, at 23:32, jason lynes :: senyl.com wrote: > > is there an easy way to limit the number of results returned when > eager loading? > > > E.g. in your code below you can''t limit the number of the article > objects returned without using subqueries and AR doesn''t currently do > that. Besides, I heard that even though MySQL "supports" subqueries > nowadays, it does next to none optimization on them so it''s actually a > lot faster to use separate queries to get the associated categories > and keywords.Why not just add support for limits regardless? It just means that MySQL queries will be slower, but other databases would be ok (I think). At least then people would have an option. Either, 1. Use limits and slow down the application because of subselects are not optimized 2. Load all objects and slow down the applications because all rows have to be loaded For people using older versions of MySQL, they will just receive an error when they try to use :limit, or :offset. - Adam
Jarkko Laine wrote:> On 6.5.2005, at 23:32, jason lynes :: senyl.com wrote: > > is there an easy way to limit the number of results returned when > eager loading? > > > Short answer: not really.Still AR accepts the :limit parameter without complaining; that should be changed.
jason lynes :: senyl.com
2005-May-09 21:47 UTC
Re: Limiting a find(:all) with eager beaver loading
how is everyone else doing this? obviously there''s a lot of eager loading going on, so how are you displaying only 10 rows per page? simply using the paginator? i am simply listing the last 10 posts on the front page, with the option to delve into the archives for more, so pagination isnt what i wanted, but I will use it if necessary.. how else are you doing it? Adam M. wrote:>Jarkko Laine wrote: > > > >>On 6.5.2005, at 23:32, jason lynes :: senyl.com wrote: >> >> is there an easy way to limit the number of results returned when >> eager loading? >> >> >>E.g. in your code below you can''t limit the number of the article >>objects returned without using subqueries and AR doesn''t currently do >>that. Besides, I heard that even though MySQL "supports" subqueries >>nowadays, it does next to none optimization on them so it''s actually a >>lot faster to use separate queries to get the associated categories >>and keywords. >> >> > > >Why not just add support for limits regardless? It just means that MySQL >queries will be slower, but other databases would be ok (I think). At >least then people would have an option. Either, > > 1. Use limits and slow down the application because of subselects >are not optimized > 2. Load all objects and slow down the applications because all rows >have to be loaded > >For people using older versions of MySQL, they will just receive an >error when they try to use :limit, or :offset. > >- Adam > > >_______________________________________________ >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
Jason, On 10.5.2005, at 00:47, jason lynes :: senyl.com wrote:> how is everyone else doing this? obviously there''s a lot of eager > loading going on, so how are you displaying only 10 rows per page? > simply using the paginator? i am simply listing the last 10 posts on > the front page, with the option to delve into the archives for more, > so pagination isnt what i wanted, but I will use it if necessary.. > how else are you doing it?paginator uses the normal find methods, it has no "magic" going on behind the curtains. Therefore it doesn''t work that well together with eager loading at the moment. I don''t know why you need eager loading for displaying the 10 latest posts but you could do something like this: @ids = ActiveRecord::Base.connection.select_all("select id from posts order by created_at desc limit 10").map {|item| item["id"]} @posts = Post.find(:all, :conditions => ["id in (?)", @ids], :include => :some_other_class) or replace the first row with: @ids = Post.find(:all, :order => "created_at desc", :limit => 10).map{|post| post.id} which is more elegant but a bit more resource intensive (although it shouldn''t matter with that few rows at all). That way you have two fairly fast db queries instead of n+1 and you can put your limits in there, too. //jarkko> > Adam M. wrote:Jarkko Laine wrote: >> >> >>> On 6.5.2005, at 23:32, jason lynes :: senyl.com wrote: >>> >>> is there an easy way to limit the number of results returned when >>> eager loading? >>> >>> >>> E.g. in your code below you can''t limit the number of the article >>> objects returned without using subqueries and AR doesn''t currently do >>> that. Besides, I heard that even though MySQL "supports" subqueries >>> nowadays, it does next to none optimization on them so it''s actually >>> a >>> lot faster to use separate queries to get the associated categories >>> and keywords. >>> >> >> Why not just add support for limits regardless? It just means that >> MySQL >> queries will be slower, but other databases would be ok (I think). At >> least then people would have an option. Either, >> >> 1. Use limits and slow down the application because of subselects >> are not optimized >> 2. Load all objects and slow down the applications because all >> rows >> have to be loaded >> >> For people using older versions of MySQL, they will just receive an >> error when they try to use :limit, or :offset. >> >> - Adam >> >> >> _______________________________________________ >> 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 >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails