Re: Selecting tree data

On 10/26/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
>
> On Oct 26, 2007, at 10:56 , Pat Maddox wrote:
>
> > A bunch of options so far...but there's really no way to do this with
> > standard SQL?
>
> What do you mean by "standard SQL"? Trees aren't inherently relational.

Right now my table looks like this:

posts
  id
  body
  parent_id
  root_id
  created_at

so if I've got the records

(1, 'post 1', NULL, 1, '4pm')
(2, 'post 2', NULL, 2, '8pm')
(3, 'post 3', 1, 1, '6pm')
(4, 'post 4', 1, 1, '5pm')
(5, 'post 5', 4, 1, '6pm')
(6, 'post 6', NULL, 1, '5pm')

I'd like to do a select and get them all in this order:

(1, 'post 1', NULL, 1, '4pm')
(4, 'post 4', 1, 1, '5pm')
(5, 'post 5', 4, 1, '6pm')
(3, 'post 3', 1, 1, '6pm')
(6, 'post 6', NULL, 1, '5pm')
(2, 'post 2', NULL, 2, '8pm')

And reverse sorted would be:

(2, 'post 2', NULL, 2, '8pm')
(6, 'post 6', NULL, 1, '5pm')
(1, 'post 1', NULL, 1, '4pm')
(3, 'post 3', 1, 1, '6pm')
(4, 'post 4', 1, 1, '5pm')
(5, 'post 5', 4, 1, '6pm')

Does that make sense?

Pat