SEO

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

- parent2
* child5
* child4
- parent1
* child3
* child2
* child1

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 happen?

Pat

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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 happen?

You need to look at the connectby function which is part of contrib.

-- Dante

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"D. Dante Lorenso" <dante@lorenso.com> 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 query do I have to run in
>> order to make it happen?
>
> 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.


--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Oct 26, 2007, at 4:19 , Gregory Stark wrote:

> "D. Dante Lorenso" <dante@lorenso.com> 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 nested intervals).

Michael Glaesemann
grzm seespotcode net



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On 10/26/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
>
> On Oct 26, 2007, at 4:19 , Gregory Stark wrote:
>
> > "D. Dante Lorenso" <dante@lorenso.com> 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 nested intervals).
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>

A bunch of options so far...but there's really no way to do this with
standard SQL?

I'm starting to feel I'm better off just pulling the data I need and
then building the tree structure in my app code.

Pat

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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 tree structure in my app code.

Part of the issue is how do you *store* the tree in the database. You
have to encode that information somehow. These are all methods to do
that.

Michael Glaesemann
grzm seespotcode net



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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', 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')
>


SELECT * FROM posts ORDER BY root_id, id;

brian

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

On 10/26/07, brian <brian@zijn-digital.com> 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')
> > (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')
> >
>
>
> 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, but what if I want to order by created_at?

btw created_at is a timestamp, I just wrote '4pm' to make it a bit
easier to read.

Pat

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Pat Maddox wrote:
> On 10/26/07, brian <brian@zijn-digital.com> 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, but what if I want to order by created_at?
>
> btw created_at is a timestamp, I just wrote '4pm' to make it a bit
> easier to read.
>

SELECT * FROM posts ORDER BY created_a, root_id, id;

brian

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend