Re: [GENERAL] Selecting tree data

2007-10-27 Thread Shane Ambler
Pat Maddox wrote: On 10/26/07, brian [EMAIL PROTECTED] wrote: Pat Maddox wrote: On 10/26/07, brian [EMAIL PROTECTED] wrote: SELECT * FROM posts ORDER BY root_id, id; brian ---(end of broadcast)--- TIP 4: Have you searched our list archives?

Re: [GENERAL] Selecting tree data

2007-10-26 Thread Gregory Stark
D. Dante Lorenso [EMAIL PROTECTED] writes: Pat Maddox wrote: I'd like to store some tree data in my database. I want to be able to sort the data but maintain a tree structure Is it possible to pull all the data like that with one query? How do I need to structure the table, and what

Re: [GENERAL] Selecting tree data

2007-10-26 Thread Michael Glaesemann
On Oct 26, 2007, at 4:19 , Gregory Stark wrote: D. Dante Lorenso [EMAIL PROTECTED] writes: You need to look at the connectby function which is part of contrib. Or ltree. Depending on how static your data is and what else you need to do with it. Or adjacency list or nested set (or even

Re: [GENERAL] Selecting tree data

2007-10-26 Thread Pat Maddox
On 10/26/07, Michael Glaesemann [EMAIL PROTECTED] wrote: On Oct 26, 2007, at 4:19 , Gregory Stark wrote: D. Dante Lorenso [EMAIL PROTECTED] writes: You need to look at the connectby function which is part of contrib. Or ltree. Depending on how static your data is and what else you

Re: [GENERAL] Selecting tree data

2007-10-26 Thread Michael Glaesemann
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. I'm starting to feel I'm better off just pulling the data I need and then building the

Re: [GENERAL] Selecting tree data

2007-10-26 Thread Pat Maddox
On 10/26/07, Michael Glaesemann [EMAIL PROTECTED] 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

Re: [GENERAL] Selecting tree data

2007-10-26 Thread brian
Pat Maddox wrote: 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',

Re: [GENERAL] Selecting tree data

2007-10-26 Thread Pat Maddox
On 10/26/07, brian [EMAIL PROTECTED] wrote: Pat Maddox wrote: 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')

Re: [GENERAL] Selecting tree data

2007-10-26 Thread brian
Pat Maddox wrote: On 10/26/07, brian [EMAIL PROTECTED] wrote: SELECT * FROM posts ORDER BY root_id, id; brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Okay,

Re: [GENERAL] Selecting tree data

2007-10-26 Thread Pat Maddox
On 10/26/07, brian [EMAIL PROTECTED] wrote: Pat Maddox wrote: On 10/26/07, brian [EMAIL PROTECTED] wrote: SELECT * FROM posts ORDER BY root_id, id; brian ---(end of broadcast)--- TIP 4: Have you searched our list archives?

[GENERAL] Selecting tree data

2007-10-25 Thread Pat Maddox
I'd like to store some tree data in my database. I want to be able to sort the data but maintain a tree structure. So for example, if I order by a timestamp, I should get - parent1 * child1 * child2 * child3 - parent2 * child4 * child5 and if I reverse the sort order, I get -

Re: [GENERAL] Selecting tree data

2007-10-25 Thread D. Dante Lorenso
Pat Maddox wrote: I'd like to store some tree data in my database. I want to be able to sort the data but maintain a tree structure Is it possible to pull all the data like that with one query? How do I need to structure the table, and what query do I have to run in order to make it