NOTE: So I partly solved this problem as I was writing it up for posting to the list - I include the original mail and my partial solution in case it is of use to anyone Has anyone got an error like this: undefined method `join'' for "discussions":String I get it when I try a join like this: def discussed @resource_pages = Paginator.new self, Resource.count( ''discussions.artifact_id = resources.id'', '', discussions''), 5, @params[''page''] @resources = Resource.find(:all, :conditions => ''discussions.artifact_id = resources.id'', :order => ''addeddate'', :include=> ''discussions'', :limit => @resource_pages.items_per_page, :offset => @resource_pages.current.offset) end So actually I realised the I was using the include incorrectly. Careful reading of the docs indicates It should be: def discussed @resource_pages = Paginator.new self, Resource.count( ''discussions.artifact_id = resources.id'', '', discussions''), 5, @params[''page''] @resources = Resource.find(:all, :conditions => ''discussions.artifact_id = resources.id'', :order => ''addeddate'', :include=> [:discussions], :limit => @resource_pages.items_per_page, :offset => @resource_pages.current.offset) end However when I run like this I get a new error: You can not use offset and limit together with has_many or has_and_belongs_to_many associations which seems a little unfair. Does anybody know why? I find that if I remove the limit and offset parameters the query does run, and the sql does use the JOIN keyword, but my pagination is broken because I was unable to limit the query. I can fix it by doing this (switching from include --> joins) def discussed @resource_pages = Paginator.new self, Resource.count( ''discussions.artifact_id = resources.id'', '', discussions''), 5, @params[''page''] @resources = Resource.find(:all, :conditions => ''discussions.artifact_id = resources.id'', :order => ''addeddate'', :joins => '', discussions'', :limit => @resource_pages.items_per_page, :offset => @resource_pages.current.offset) end but the sql generated doesn''t use the join keyword (in mysql): SELECT * FROM resources , discussions WHERE discussions.artifact_id resources.id ORDER BY addeddate LIMIT 5 so this superficially fixes my problem, but I wondered if anyone had experienced similar, or could explain to me the reason why rails/active-record is preventing me from using limit/offset with actual joins - the database doesn''t have a problem with it, e.g. SELECT discussions.deletion_date AS t1_r7, resources.subject AS t0_r6, discussions.message_count AS t1_r8, resources.author AS t 0_r7, resources.publisher AS t0_r8, resources.year AS t0_r10, resources.place AS t0_r9, discussions.discussion_id AS t1_r0, resources.addeddate AS t0_r11, discussions.parent_id AS t1_r1, resources.last_modified_date AS t0_r12, resources.id AS t0_r0, discussions.user_id AS t1_r2, resources.deletion_date AS t0_r13, resources.type AS t0_r1, discussions.title AS t1_r3, resources.userid AS t0_r14, resources.title AS t0_r2, discussions.description AS t1_r4, resources.rating AS t0_r15, resources.description AS t0_r3, discussions.artifact_id AS t1_r5, resources.public AS t0_r16, resources.audience AS t0_r4, discussions.creation_date AS t1_r6, resources.url AS t0_r5 FROM resources LEFT OUTER JOIN discussions ON discussi ons.artifact_id = resources.id WHERE discussions.artifact_id resources.id ORDER BY addeddate LIMIT 5 (the rails query with an added LIMIT) runs just fine ... Many thanks in advance CHEERS> SAM
On 29 Sep 2005, at 18:59, Sam Joseph wrote:> You can not use offset and limit together with has_many or > has_and_belongs_to_many associations > > which seems a little unfair. Does anybody know why?I think so. The reason is, limit and offset control the number of rows returned from the SQL query. Including an association creates multiple returned rows per object - instead of one row for your object, you get N rows because of N has_many or has_and_belongs_to_many associated objects. Rails is cleverly packaging this result set into a single object + N associations, but the limit and join parameters would need to happen before this, and they cannot know the size of N. As you surmise, you will need to do the limit and offset in Rails, rather than in SQL. Many of us are, I think, doing this with paginate_collection, added to application.rb: def paginate_collection(collection, options = {}) default_options = {:per_page => 10, :page => 1} options = default_options.merge options pages = Paginator.new self, collection.size, options[:per_page], options[:page] first = pages.current.offset last = [first + options[:per_page], collection.size].min slice = collection[first...last] return [pages, slice] end If you''re using MySQL and it deems the query to be one it can cache, the second page will not trigger the query again - you''ll get results from MySQL''s cache. Hope this helps - and if anyone has anything else to add, add me to the list of interested parties. Mike
Sam, The :joins option for finds just puts the string you assigned it into the query. So you probably should use: :joins => ''INNER JOIN discussions ON resources.id = discussions.resource_id'' You can''t use paging yet with :include for has_many or has_and_belongs_to_many associations because the counting would be wrong. The query will run, but will not return the results you expect. If you have 5 resources each with 5 discussions, 25 rows will be returned with the include query, so you would end up paging through duplicate resource records. There is a ticket out there about supporting paging with includes: http://dev.rubyonrails.com/ticket/1851 Hope that helps. -Lee On 9/29/05, Sam Joseph <gaijin-EXQg2k7MwXtHfZP73Gtkiw@public.gmane.org> wrote:> NOTE: So I partly solved this problem as I was writing it up for posting > to the list - I include the original mail and my partial solution in > case it is of use to anyone > > Has anyone got an error like this: > > undefined method `join'' for "discussions":String > > I get it when I try a join like this: > > def discussed > @resource_pages = Paginator.new self, Resource.count( > ''discussions.artifact_id = resources.id'', > '', discussions''), 5, @params[''page''] > @resources = Resource.find(:all, :conditions => ''discussions.artifact_id > = resources.id'', :order => ''addeddate'', > :include=> ''discussions'', :limit => @resource_pages.items_per_page, > :offset => @resource_pages.current.offset) > end > > So actually I realised the I was using the include incorrectly. Careful > reading of the docs indicates It should be: > > def discussed > @resource_pages = Paginator.new self, Resource.count( > ''discussions.artifact_id = resources.id'', > '', discussions''), 5, @params[''page''] > @resources = Resource.find(:all, :conditions => ''discussions.artifact_id > = resources.id'', :order => ''addeddate'', > :include=> [:discussions], :limit => @resource_pages.items_per_page, > :offset => @resource_pages.current.offset) > end > > However when I run like this I get a new error: > > You can not use offset and limit together with has_many or has_and_belongs_to_many associations > > which seems a little unfair. Does anybody know why? I find that if I > remove the limit and offset parameters the query does run, and the sql > does use the JOIN keyword, but my pagination is broken because I was > unable to limit the query. > > I can fix it by doing this (switching from include --> joins) > > def discussed > @resource_pages = Paginator.new self, Resource.count( > ''discussions.artifact_id = resources.id'', > '', discussions''), 5, @params[''page''] > @resources = Resource.find(:all, :conditions => ''discussions.artifact_id > = resources.id'', :order => ''addeddate'', > :joins => '', discussions'', :limit => @resource_pages.items_per_page, > :offset => @resource_pages.current.offset) > end > > but the sql generated doesn''t use the join keyword (in mysql): > > SELECT * FROM resources , discussions WHERE discussions.artifact_id > resources.id ORDER BY addeddate LIMIT 5 > > so this superficially fixes my problem, but I wondered if anyone had > experienced similar, or could explain to me the reason why > rails/active-record is preventing me from using limit/offset with actual > joins - the database doesn''t have a problem with it, e.g. > > SELECT discussions.deletion_date AS t1_r7, resources.subject AS t0_r6, > discussions.message_count AS t1_r8, resources.author AS t > 0_r7, resources.publisher AS t0_r8, resources.year AS t0_r10, > resources.place AS t0_r9, discussions.discussion_id AS t1_r0, > resources.addeddate AS t0_r11, discussions.parent_id AS > t1_r1, resources.last_modified_date AS t0_r12, resources.id AS t0_r0, > discussions.user_id AS t1_r2, resources.deletion_date AS t0_r13, > resources.type AS t0_r1, discussions.title AS > t1_r3, resources.userid AS t0_r14, resources.title AS t0_r2, > discussions.description AS t1_r4, resources.rating AS t0_r15, > resources.description AS t0_r3, discussions.artifact_id > AS t1_r5, resources.public AS t0_r16, resources.audience AS t0_r4, > discussions.creation_date AS t1_r6, resources.url AS t0_r5 FROM > resources LEFT OUTER JOIN discussions ON discussi > ons.artifact_id = resources.id WHERE discussions.artifact_id > resources.id ORDER BY addeddate LIMIT 5 > > (the rails query with an added LIMIT) runs just fine ... > > Many thanks in advance > CHEERS> SAM > > > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On Thursday 29 September 2005 22:08, Michael Houghton wrote:> On 29 Sep 2005, at 18:59, Sam Joseph wrote: > > You can not use offset and limit together with has_many or > > has_and_belongs_to_many associations > > > > which seems a little unfair. Does anybody know why? > > I think so. The reason is, limit and offset control the number of > rows returned from the > SQL query. > > Including an association creates multiple returned rows per object - > instead of one row > for your object, you get N rows because of N has_many or > has_and_belongs_to_many > associated objects.That only means that a simple limit/offset on the SELECT won''t do. As I''ve just written a patch[*] that handles this with a sub-select. Unfortunately, less capable DBMSs (MySQL) apparently can''t handle this. PostgreSQL can.> As you surmise, you will need to do the limit and offset in Rails, > rather than in SQL.Yes, that''s an alternative and I''ve actually implemented it (see below for a version where I hope to have removed the BoilerPlate specifics). But it should really be in Rails, not in an application using Rails. For one thing, this limitation violates the Priniple of Least Surprise. Try this link, to see why http://weblog.rubyonrails.com/archives/2005/09/29/ Another reason is that extracting only the wanted objects from a complete result set of all objects is incredibly expensive. Rails has to instantiate all these objects -- something it is rather slow at (see again [*]). In my, possibly skewed, experience object instantiation in Rails is slower than the database query. Within Rails it is possible to avoid unnecessary instantiations. In application code it is not possible. Michael def find_objects(klass, query_options, items_per_page = 0, current_page = 1) objects = count = paginator = nil current_page = 1 unless current_page if items_per_page == 0 || has_unlimitable_associations?(klass, query_options) objects = klass.find(:all, query_options) count = objects.size if items_per_page > 0 paginator = ActionController::Pagination::Paginator.new( self, count, items_per_page, current_page) objects = objects[paginator.current.offset, items_per_page] end else count = klass.count(query[:conditions], query[:joins], query[:include]) paginator = ActionController::Pagination::Paginator.new(self, count, items_per_page, current_page) query[:offset] ||= paginator.current.offset query[:limit] ||= items_per_page objects = find_all_objects(list_spec.klass, query) end return objects, count, paginator end def has_unlimitable_associations?(klass, options) reflections = [ options[:include] ].flatten. compact.collect { |association| klass.reflect_on_association(association.to_s.intern) } reflections.reject { |r| [ :belongs_to, :has_one ].include?(r.macro) }.length > 0 end [*] http://dev.rubyonrails.org/ticket/2172 -- Michael Schuerig Life is just as deadly mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org As it looks http://www.schuerig.de/michael/ --Richard Thompson, Sibella