Rails - Jun 2006 - Converting from acts_as_tree to acts_as_nested_set

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.

-- 
Posted via http://www.ruby-forum.com/.

HI Ian,


Le 20 juin 06, ? 21:41, Ian Zabel a ?crit :
> 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 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 Michel
--
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

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

The LOOP statement is not available in 4.x, among other things.

Thanks,

Ian.

-- 
Posted via http://www.ruby-forum.com/.