Selecting tree data

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

"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

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

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

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

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

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

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

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

On 10/26/07, brian <brian@zijn-digital.com> wrote:

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;

That doesn't work because it just sorts by created_at, and then if two
records are the same it goes to root_id. That's not what I want.

Pat

Pat Maddox wrote:

On 10/26/07, brian <brian@zijn-digital.com> wrote:

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;

That doesn't work because it just sorts by created_at, and then if two
records are the same it goes to root_id. That's not what I want.

You have been a little vague on what you are doing and what you want to
achieve - I think that if you describe exactly what you want to achieve
you will find some more help - but then at that stage you may click as
to how to achieve it anyway.

I am guessing that
root_id is the id of the first post starting the discussion
parent_id is the the post that is being responded to

root_id would be NOT NULL and would be the same as id for the first post.
I would say that parent_id SHOULD also be NOT NULL and be the same as
the id for the parent post. The lack of NULL's here would help your
sorting to go the way you want.

My guess is you want
SELECT * FROM posts ORDER BY root_id, parent_id, created_at, id;

which you would reverse as
SELECT * FROM posts ORDER BY root_id desc, parent_id desc, created_at
desc, id desc;

By the way - timestamp is accurate to 1 microsecond (I am guessing that
would be the creation timestamp) and if you manage to have two identical
timestamps the id column would tell the exact order of creation if you
wanted to be that picky. You could actually not use the created_at in
the sorting as the id will give the same effect by itself.

--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz