On 7/8/07, Steffan A. Cline <steffan@stripped> wrote:
> I am trying to set up a forum where there are main posts with replies and
> replies to replies. Rather than using nested queries in my middleware I am
> thinking there has to be some kind of recursive query where I can output
> results in a format like so:
> Reply to main
> reply to reply to main
> reply to main
> reply to 2nd reply to main
As another poster said, there are various ways to represent trees in
databases. You may want to consult some of these references. In this
particular case, nothing comes to mind that will give you both:
a)The ability to represent an arbitrarily-deep hierarchy of responses, AND
b)Will let you get the entire result set ordered the way you want in ONE
I don't immediately see how to get both at the same time.
If, for example, you were willing to sacrifice (a), then just set up
integers (maybe 3 of them) allowing you to represent a nesting 3 deep then
order by these integers on the query. But "3" is not "arbitrarily-deep".
If you were willing to sacrifice (b), then you could just represent the tree
by a table of "links" that relate parent and child. Finding all the
children for a parent is just "select * from links where parent=29331" or
something like this. Problem is, you would need to issue queries to
traverse the tree.
I can't immediately see a way to get both (a) and (b) simultaneously. But
you can definitely get EITHER (a) or (b).