Converting from acts_as_tree to acts_as_nested_set

Rails
1

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.

2

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

3

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 :frowning:

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.