Hello there! I''ve been working on some rails-based software to balance my budget, and I''ve found myself in a bit of a fix. I was curious as whether any bright folk out there could help me find a solutions Some background first. I have a tree of accounts, which essentially looks a little like this. CREATE TABLE accounts ( id INT auto_increment, parent_id INT, name VARCHAR(128), PRIMARY KEY (id) ); class Account < ActiveRecord::Base acts_as_tree :order => "name" def each (&block) yield self for child in children child.each(&block) end end end Whenever I want to view the account tree, I use something akin to the Account.each method shown above to iterate through an account''s descendants. The problem with this is that to fetch the children of an account, rails uses a SELECT statement. Thus, as my class stands, it fires of an SQL query for each and every account I have, every time I want to view the account tree. Whilst my accounts are unlikely to number more than two dozen, two dozen SQL queries for each page visited seems a little excessive! Does anyone, smarter than I, have any ideas of how to solve this tricky problem? (I am attempting to store serialized Account objects in a database cache table in an order to solve the problem, but I have had limited success so far.) - Jim
On 7/20/05, James Reeves <jreeves-IHxuefqFUSf+uv41P6q33PXRex20P6io@public.gmane.org> wrote:> Hello there! > > I''ve been working on some rails-based software to balance my budget, and I''ve > found myself in a bit of a fix. I was curious as whether any bright folk out > there could help me find a solutions > > Some background first. I have a tree of accounts, which essentially looks a > little like this. > > CREATE TABLE accounts ( > id INT auto_increment, > parent_id INT, > name VARCHAR(128), > PRIMARY KEY (id) > ); > > class Account < ActiveRecord::Base > acts_as_tree :order => "name" > > def each (&block) > yield self > for child in children > child.each(&block) > end > end > end > > Whenever I want to view the account tree, I use something akin to the > Account.each method shown above to iterate through an account''s descendants. > > The problem with this is that to fetch the children of an account, rails uses > a SELECT statement. Thus, as my class stands, it fires of an SQL query for > each and every account I have, every time I want to view the account tree. > > Whilst my accounts are unlikely to number more than two dozen, two dozen SQL > queries for each page visited seems a little excessive! > > Does anyone, smarter than I, have any ideas of how to solve this tricky > problem? > > (I am attempting to store serialized Account objects in a database cache table > in an order to solve the problem, but I have had limited success so far.)Try using acts_as_nested_set. It uses a more complicated approach to representing a hierarchy in a database, but allows you to select all the children of a node in a single query: http://rails.rubyonrails.com/classes/ActiveRecord/Acts/NestedSet/ClassMethods.html -- rick http://techno-weenie.net
Be aware that the nested_set method will slow your updates significantly, because every single row has to be updated on every change in position, removal or addition. Joshua Sierles _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Be aware that the nested_set method will slow your updates significantly, because every single row has to be updated on every change in position, removal or addition. Joshua Sierles _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Be aware that the nested_set method will slow your updates significantly, because every single row has to be updated on every change in position, removal or addition. Joshua Sierles _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 7/20/05, Joshua Sierles <jsierles-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Be aware that the nested_set method will slow your updates significantly, > because every single row > has to be updated on every change in position, removal or addition.Ah yes, good point! Two things in addition: transactions will speed this process up. Also, if your list isn''t updated all that often, it''s not even a big deal. I''ve implemented nested set structures with PHP/sqlite and ASP.Net/MSSQL and the update time wasn''t that noticeable. I even tried a more intensive test with ASP.Net and thousands of categories, and transactions helped immensely. It was actually quicker to do this than a recursive stored procedure (though, I''m not sure if this says something about MSSQL''s server cursor performance or what). Since the multiple SELECTs with acts_as_list is bugging you now, I''d suggest at least trying nested_set out and only worrying about the update performance if it''s noticeable. -- rick http://techno-weenie.net
now if all those accounts had transactions, how would you get all the transactions for a set of accounts as a single query? On 7/21/05, Rick Olson <technoweenie-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 7/20/05, James Reeves <jreeves-IHxuefqFUSf+uv41P6q33PXRex20P6io@public.gmane.org> wrote: > > Hello there! > > > > I''ve been working on some rails-based software to balance my budget, and I''ve > > found myself in a bit of a fix. I was curious as whether any bright folk out > > there could help me find a solutions > > > > Some background first. I have a tree of accounts, which essentially looks a > > little like this. > > > > CREATE TABLE accounts ( > > id INT auto_increment, > > parent_id INT, > > name VARCHAR(128), > > PRIMARY KEY (id) > > ); > > > > class Account < ActiveRecord::Base > > acts_as_tree :order => "name" > > > > def each (&block) > > yield self > > for child in children > > child.each(&block) > > end > > end > > end > > > > Whenever I want to view the account tree, I use something akin to the > > Account.each method shown above to iterate through an account''s descendants. > > > > The problem with this is that to fetch the children of an account, rails uses > > a SELECT statement. Thus, as my class stands, it fires of an SQL query for > > each and every account I have, every time I want to view the account tree. > > > > Whilst my accounts are unlikely to number more than two dozen, two dozen SQL > > queries for each page visited seems a little excessive! > > > > Does anyone, smarter than I, have any ideas of how to solve this tricky > > problem? > > > > (I am attempting to store serialized Account objects in a database cache table > > in an order to solve the problem, but I have had limited success so far.) > > Try using acts_as_nested_set. It uses a more complicated approach to > representing a hierarchy in a database, but allows you to select all > the children of a node in a single query: > > http://rails.rubyonrails.com/classes/ActiveRecord/Acts/NestedSet/ClassMethods.html > > -- > rick > http://techno-weenie.net > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On 7/20/05, Keith Nicholas <keith.nicholas-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> now if all those accounts had transactions, how would you get all the > transactions for a set of accounts as a single query?By transactions I mean wrapping the whole update process in an AR transaction: ListModel.transaction do @listmodel.save # updates left/right values in the whole tree end As for how it selects all child nodes in a single query, I''d suggest reading the docs that explain it: http://rails.rubyonrails.com/classes/ActiveRecord/Acts/NestedSet/ClassMethods.html http://threebit.net/tutorials/nestedset/tutorial1.html -- rick http://techno-weenie.net
Rick Olson wrote:> Try using acts_as_nested_set. It uses a more complicated approach to > >representing a hierarchy in a database, but allows you to select all >the children of a node in a single query: > >http://rails.rubyonrails.com/classes/ActiveRecord/Acts/NestedSet/ClassMethods.html >The rails book has this footnote on page 244. "as this book was being finalized, the nested set variant had some serious problems that prevent us from verifying its use with working code." Is this still the case? And if so, what are the known problems? I''ve worked with nested sets a lot in other languages, so it would be good to have a working version in Rails. -- R.Livsey http://livsey.org
Caching also helps. On 7/20/05, James Reeves <jreeves-IHxuefqFUSf+uv41P6q33PXRex20P6io@public.gmane.org> wrote:> Hello there! > > I''ve been working on some rails-based software to balance my budget, and I''ve > found myself in a bit of a fix. I was curious as whether any bright folk out > there could help me find a solutions > > Some background first. I have a tree of accounts, which essentially looks a > little like this. > > CREATE TABLE accounts ( > id INT auto_increment, > parent_id INT, > name VARCHAR(128), > PRIMARY KEY (id) > ); > > class Account < ActiveRecord::Base > acts_as_tree :order => "name" > > def each (&block) > yield self > for child in children > child.each(&block) > end > end > end > > Whenever I want to view the account tree, I use something akin to the > Account.each method shown above to iterate through an account''s descendants. > > The problem with this is that to fetch the children of an account, rails uses > a SELECT statement. Thus, as my class stands, it fires of an SQL query for > each and every account I have, every time I want to view the account tree. > > Whilst my accounts are unlikely to number more than two dozen, two dozen SQL > queries for each page visited seems a little excessive! > > Does anyone, smarter than I, have any ideas of how to solve this tricky > problem? > > (I am attempting to store serialized Account objects in a database cache table > in an order to solve the problem, but I have had limited success so far.) > > - Jim > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On Wednesday 20 Jul 2005 22:17, Rick Olson wrote:> Try using acts_as_nested_set. It uses a more complicated approach to > representing a hierarchy in a database, but allows you to select all > the children of a node in a single query:Unfortunately, as far as I can make out, acts_as_nested_set doesn''t preserve hierarchy when selecting its children. You can select all the children of a node in a single query, but it seems to returns them as a flat list. I think I''ll steer clear of acts_as_nested_set. Looking at it, I''ll need to write custom "parent" and "children" methods to maintain object hierarchy, and that seems like more trouble than this is worth. The path of least work in this case, I think, is to write a cache-friendly wrapper class which I can use to ferry an act_as_tree Activerecord object through Marshal with all of its descendants in tow. Caching is probably simpler in this case. - Jim
I don''t know if it helps anybody but as nested_set stands today there is no way of getting the parents of a node. I have written a method that does just that but, as the all_children method does, it returns a flat array. For my purposes it''s actually what I need. One problem, though, I haven''t figured out how to move sets of nodes around and nested_set does not seem to be able to handle that. Adrian Madrid On 7/21/05, James Reeves <jreeves-IHxuefqFUSf+uv41P6q33PXRex20P6io@public.gmane.org> wrote:> > On Wednesday 20 Jul 2005 22:17, Rick Olson wrote: > > Try using acts_as_nested_set. It uses a more complicated approach to > > representing a hierarchy in a database, but allows you to select all > > the children of a node in a single query: > > Unfortunately, as far as I can make out, acts_as_nested_set doesn''t > preserve > hierarchy when selecting its children. You can select all the children of > a > node in a single query, but it seems to returns them as a flat list. > > I think I''ll steer clear of acts_as_nested_set. Looking at it, I''ll need > to > write custom "parent" and "children" methods to maintain object hierarchy, > and that seems like more trouble than this is worth. > > The path of least work in this case, I think, is to write a cache-friendly > wrapper class which I can use to ferry an act_as_tree Activerecord object > through Marshal with all of its descendants in tow. Caching is probably > simpler in this case. > > - Jim > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Adrian Esteban Madrid aemadrid-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
If you haven''t implemented your solution yet, this ''sqltree'' library works: http://rubyforge.org/frs/?group_id=835 It preserves hierarchy, but requires MySQL. -lv James Reeves wrote:> On Wednesday 20 Jul 2005 22:17, Rick Olson wrote: > >>Try using acts_as_nested_set. It uses a more complicated approach to >>representing a hierarchy in a database, but allows you to select all >>the children of a node in a single query: > > > Unfortunately, as far as I can make out, acts_as_nested_set doesn''t preserve > hierarchy when selecting its children. You can select all the children of a > node in a single query, but it seems to returns them as a flat list. > > I think I''ll steer clear of acts_as_nested_set. Looking at it, I''ll need to > write custom "parent" and "children" methods to maintain object hierarchy, > and that seems like more trouble than this is worth. > > The path of least work in this case, I think, is to write a cache-friendly > wrapper class which I can use to ferry an act_as_tree Activerecord object > through Marshal with all of its descendants in tow. Caching is probably > simpler in this case. > > - Jim