Self-referencing table - how to process?

I have a self-referencing table ‘Properties’ and I want to build a
hierarchy of all properties starting at the lowest in the tree. What is
the best approach to this is Rails?

for example:

“properties” table

id|name|belongs_to_id
1|item1|NULL
2|item 2|1
3|item 3|NULL
4|item 4|2

So how would I retrieve the related rows with ids 1,2,4 in such a way
that I can easily build a tree-like list such as:

1
…2
…4

Thanks,
Lee

Lee wrote:

I have a self-referencing table ‘Properties’ and I want to build a
hierarchy of all properties starting at the lowest in the tree. What is
the best approach to this is Rails?

for example:

“properties” table

id|name|belongs_to_id
1|item1|NULL
2|item 2|1
3|item 3|NULL
4|item 4|2

So how would I retrieve the related rows with ids 1,2,4 in such a way
that I can easily build a tree-like list such as:

Rails has two features that can be used for this: acts_as_tree and
acts_as_nested_set (these are slated to be moved into plugins soon).
Trees use a schema like the one you describe, and are fine for
situations where you only want to know the immediate children of a node
in the tree. Nested Set uses a mathematical set notation to describe
parent-child relationships that makes it possible to retrieve all the
children in the sub-tree below a node in just one query, but at the
expense of having to update many rows to insert one new record. Take
your pick.


Josh S.
http://blog.hasmanythrough.com