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.