# Converting from acts\_as\_tree to acts\_as\_nested\_set **URL:** \ **Category:** Rails\ **Created:** [June 20, 2006, 9:41pm UTC](https://www.ruby-forum.com/t/converting-from-acts-as-tree-to-acts-as-nested-set/63957 "2006-06-20T21:41:48Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![ianzabel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/b77776/32.png) [@ianzabel](https://www.ruby-forum.com/u/ianzabel)\ **Post date:** [June 20, 2006, 9:41pm UTC](https://www.ruby-forum.com/t/converting-from-acts-as-tree-to-acts-as-nested-set/63957/1 "2006-06-20T21:41:48Z")
I’m currently using acts\_as\_tree to display threaded Comments on my forums-like site. It’s waaay too slow to display a page with 1,000 comments, as it’s issuing a TON of selects. I’m pretty sure I want to convert to the nested set model, using acts\_as\_nested\_set or acts\_as\_threaded. This should give me the performance I’m looking for. The problem, of course, is the adjacency model that is used by acts\_as\_tree only has parent\_id, it doesn’t have the left/right data needed by the nested set model. Has anyone seen or written an algorithm that can process a rails tree into a nested set by determining the values for left/right and filling them in? I’ve seen a SQL method to accomplish this conversion, but it will not run on MySQL. I think I may be forced to do the conversion using Ruby. As a side note: My comments DO have a root\_id available, so it is actually possible for me to select all Comments for a topic in a single select. It may be possible to then work out the tree model in memory based on the parent\_id values. Would you recommend trying to work this into an enhanced acts\_as\_tree plugin, or just would it just be better to convert my data to a nested set? Any help or opinions are appreciated. Ian. ---
**Author:** ![ianzabel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/b77776/32.png) [@ianzabel](https://www.ruby-forum.com/u/ianzabel)\ **Post date:** [July 4, 2006, 11:01am UTC](https://www.ruby-forum.com/t/converting-from-acts-as-tree-to-acts-as-nested-set/63957/2 "2006-07-04T11:01:27Z")
HI Ian, Le 20 juin 06, à 21:41, Ian Z. a écrit : > them in > I’ve already done such conversions, using both sql and ruby. > Can you provide a sql query that would return the whole tree ordered ? > If you use mysql \>= 4 you can have subselects to get the order > numbering and the number of children for each parent; then you get all > this with a find\_by\_sql and loop on the items, attributing lft and rgt > for each one. In my case it was a tree of uris like a directory. > I’ve seen a SQL method to accomplish this conversion, but it will not > run on MySQL. I think I may be forced to do the conversion using Ruby. What are the problems preventing such sql conversion ? ## Jean-Christophe M. Symétrie, édition de musique et services multimédia 30 rue Jean-Baptiste Say 69001 LYON (FRANCE) tél +33 (0)478 29 52 14 fax +33 (0)478 30 01 11 web [www.symetrie.com](http://www.symetrie.com) ---
**Author:** ![ianzabel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/b77776/32.png) [@ianzabel](https://www.ruby-forum.com/u/ianzabel)\ **Post date:** [July 4, 2006, 4:03pm UTC](https://www.ruby-forum.com/t/converting-from-acts-as-tree-to-acts-as-nested-set/63957/3 "2006-07-04T16:03:34Z")
Jean-Christophe, > Can you provide a sql query that would return the whole tree ordered ? > If you use mysql \>= 4 you can have subselects to get the order > numbering and the number of children for each parent; then you get all > this with a find\_by\_sql and loop on the items, attributing lft and rgt > for each one I am using MySQL 4.1.19. So it _is_ possible, then? Interesting. I’m not familiar enough with all of the syntax to piece it all together. Currently I’m using myisam tables, but can convert to innodb if that is necessary. I have a comments table that looks like this: CREATE TABLE `comments` ( `id` int(11) NOT NULL auto\_increment, `root_id` int(11) NOT NULL default ‘0’, `commentable_id` int(11) default NULL, `commentable_type` varchar(255) default NULL, `parent_id` int(11) default NULL, […snip…] ) The parent\_id column is a self-reference to id that creates the adjacency model. commentable\_id and commentable\_type are used to define the sets of comments that are attached to a model through a polymorphic association. root\_id is a foreign key to the id of the first comment in a particular comment thread. So, to grab the comments for a commentable article in my app, I’ll do a query like so: select \* from comments where commentable\_id = ? and commentable\_type = ? To get only the root, or top-level comments: select \* from comments where commentable\_id = ? and commentable\_type = ? and parent\_id is null Unfortunately, I can’t figure out how to construct a query that gets all the comments in the correct tree order. Can’t do it with ORDER BY, as far as I can tell. I guess it would have to be done with subqueries, similar to how I display the tree using recursive selects. Sorry ☹ Here is sample data for the query: SELECT id, parent\_id, root\_id FROM comments where commentable\_id = 4041 and commentable\_type = ‘Topic’ ±-------±----------±--------+ | id | parent\_id | root\_id | ±-------±----------±--------+ | 284883 | NULL | NULL | | 284885 | NULL | NULL | | 284903 | 284883 | 284883 | | 284904 | 284885 | 284885 | ±-------±----------±--------+ The correct order would be : ±-------±----------±--------+ | id | parent\_id | root\_id | ±-------±----------±--------+ | 284883 | NULL | NULL | | 284903 | 284883 | 284883 | | 284885 | NULL | NULL | | 284904 | 284885 | 284885 | ±-------±----------±--------+ > What are the problems preventing such sql conversion ? I’ve been looking at this basic algorithm. [http://lists.nyphp.org/pipermail/org/2003-October/001362.html](http://lists.nyphp.org/pipermail/org/2003-October/001362.html) The LOOP statement is not available in 4.x, among other things. Thanks, Ian.