Обсуждение: Selecting tree data
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
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