Hi, currently the eager association loading via ":include" does not work when a has-many or habtm association is combined with offset/limit (which is required for paginations). This is because in these cases, the result set will contain more than one row per object, but limit/offset works on row numbers. I''ve developed a patch that makes this work by using 2 SQL statements instead of 1. In a first statement, the object is loaded together with has-one and belongs-to relations using the normal limit/offset technique. In a second step, objects related via has-many or habtm relations are loaded in a single SQL statement. This is done using the "primary_key IN ( list of ids of objects loaded in step 1)" construct. What do people think about this? Should this be included into rails? The patch is basically working but needs some more polishing before it can be published. It could result in problems, when you are using custom WHERE conditions, that reference columns in the associated tables: Since some of the tables only appear in the first and some only in the second statement, the where condition could produce an error. On the other hand, I believe this to be pretty much an edge case and since it did not work at all before, it wouldn''t break anything. Sebastian
I use this in my code with a subselect (don''t know if all DBs can tackle this). It works like this: SELECT * FROM x WHERE id IN (SELECT id FROM x WHERE <real-where-args> ORDER BY o LIMIT n OFFSET m) <joins>. I''ve made a short blog entry on this a while ago: http://mir.aculo.us/articles/2005/06/12/activerecord-eager-loading- and-sql-limit-offset (never mind the comments, grrr). :) Thomas Am 26.07.2005 um 15:03 schrieb Sebastian Kanthak:> Hi, > > currently the eager association loading via ":include" does not > work when a has-many or habtm association is combined with offset/ > limit (which is required for paginations). This is because in these > cases, the result set will contain more than one row per object, > but limit/offset works on row numbers. > > I''ve developed a patch that makes this work by using 2 SQL > statements instead of 1. In a first statement, the object is loaded > together with has-one and belongs-to relations using the normal > limit/offset technique. In a second step, objects related via has- > many or habtm relations are loaded in a single SQL statement. This > is done using the "primary_key IN ( list of ids of objects loaded > in step 1)" construct. > > What do people think about this? Should this be included into > rails? The patch is basically working but needs some more polishing > before it can be published. > > It could result in problems, when you are using custom WHERE > conditions, that reference columns in the associated tables: Since > some of the tables only appear in the first and some only in the > second statement, the where condition could produce an error. On > the other hand, I believe this to be pretty much an edge case and > since it did not work at all before, it wouldn''t break anything. > > Sebastian > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Crap, this is the first comment spam i see in a typo blog. Ah well, been fun :( On 7/26/05, Thomas Fuchs <thomas-9D208sng4xU@public.gmane.org> wrote:> I use this in my code with a subselect (don''t know if all DBs can > tackle this). It works like this: > > SELECT * FROM x WHERE id IN (SELECT id FROM x WHERE <real-where-args> > ORDER BY o LIMIT n OFFSET m) <joins>. > > I''ve made a short blog entry on this a while ago: > http://mir.aculo.us/articles/2005/06/12/activerecord-eager-loading- > and-sql-limit-offset > > (never mind the comments, grrr). :) > > > Thomas > > Am 26.07.2005 um 15:03 schrieb Sebastian Kanthak: > > > Hi, > > > > currently the eager association loading via ":include" does not > > work when a has-many or habtm association is combined with offset/ > > limit (which is required for paginations). This is because in these > > cases, the result set will contain more than one row per object, > > but limit/offset works on row numbers. > > > > I''ve developed a patch that makes this work by using 2 SQL > > statements instead of 1. In a first statement, the object is loaded > > together with has-one and belongs-to relations using the normal > > limit/offset technique. In a second step, objects related via has- > > many or habtm relations are loaded in a single SQL statement. This > > is done using the "primary_key IN ( list of ids of objects loaded > > in step 1)" construct. > > > > What do people think about this? Should this be included into > > rails? The patch is basically working but needs some more polishing > > before it can be published. > > > > It could result in problems, when you are using custom WHERE > > conditions, that reference columns in the associated tables: Since > > some of the tables only appear in the first and some only in the > > second statement, the where condition could produce an error. On > > the other hand, I believe this to be pretty much an edge case and > > since it did not work at all before, it wouldn''t break anything. > > > > Sebastian > > > > _______________________________________________ > > 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 >-- Tobi http://www.snowdevil.ca - Snowboards that don''t suck http://typo.leetsoft.com - Open source weblog engine http://blog.leetsoft.com - Technical weblog
On 7/26/05, Thomas Fuchs <thomas-9D208sng4xU@public.gmane.org> wrote:>>I use this in my code with a subselect (don''t know if all DBs can >>tackle this). It works like this: >> >>SELECT * FROM x WHERE id IN (SELECT id FROM x WHERE <real-where-args> >>ORDER BY o LIMIT n OFFSET m) <joins>. >> >>I''ve made a short blog entry on this a while ago: >>http://mir.aculo.us/articles/2005/06/12/activerecord-eager-loading- >>and-sql-limit-offset >> >>MySQL supports sub-selects as of version 4.1. Since older versions are still pretty common, I''d prefer the 2-statement strategy. Sebastian
On 7/26/05, Sebastian Kanthak <sebastian.kanthak-ZS8b95Whz3sUSW6y5lq3GQ@public.gmane.org> wrote:> On 7/26/05, Thomas Fuchs <thomas-9D208sng4xU@public.gmane.org> wrote: > > >>I use this in my code with a subselect (don''t know if all DBs can > >>tackle this). It works like this: > >> > >>SELECT * FROM x WHERE id IN (SELECT id FROM x WHERE <real-where-args> > >>ORDER BY o LIMIT n OFFSET m) <joins>. > >> > >>I''ve made a short blog entry on this a while ago: > >>http://mir.aculo.us/articles/2005/06/12/activerecord-eager-loading- > >>and-sql-limit-offset > >> > >> > MySQL supports sub-selects as of version 4.1. Since older versions are > still pretty common, I''d prefer the 2-statement strategy. >mysql supports sub-selects, but only minimally. sub-select queries will be entirely unoptimized, will not use indexes, etc. maybe the first version can use a subselect, but perhaps for mysql we can introduce some kind of third query (trust me, it will be a lot faster) that generates a sequence of IDs, so that your final query is something like select * from table where id in (1,2,52,54,64). these types of queries are, fortunately, fast.
Courtenay wrote:>mysql supports sub-selects, but only minimally. sub-select queries >will be entirely unoptimized, will not use indexes, etc. > > >and only starting at version 4.1, I believe. Thanks for the information about it being still unoptimized.>maybe the first version can use a subselect, but perhaps for mysql we >can introduce some kind of third query (trust me, it will be a lot >faster) that generates a sequence of IDs, so that your final query is >something like select * from table where id in (1,2,52,54,64). > >that''s exactly, what my approach is doing. That''s why it needs 2 queries: One for loading objects from the base tables with limit and offset and another one, for loading has_many and habtm relations for these objects via the "id in ( ... ) " construct. Works nicely so far, will submit a patch later this day. Sebastian