Thread
-
hierarchy select question?
database@gurubase.com — 2000-07-31T19:15:40Z
Dear all, I would like to define threads in message system for replies to message but if I define too many level, I am afraid I have problem in the select... Say, I have define 3 levels: 1 / \ 2 3 /\ \ 4 5 6 It means message 2 is a reply to 1. 4 is a further follow-up of 2...etc In table format, I would present it with Table A ParentId ChildId 1 2 1 3 2 4 2 5 3 6 I think I can issue the command to join table A 3 times to give the following result 1st 2nd 3rd Row1 1 2 4 Row2 1 2 5 Row3 1 3 6 But can I show a. which level each node belongs b. no. of child nodes it has altogether in one "select" sql and if the level exceeds 3, how can I do it? Many thanks..... Best regards, Boris -
Re: hierarchy select question?
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> — 2000-08-01T00:01:57Z
Hi, Originally postgres had a "recursive select" to handle cases like this. Some syntax like... retrieve* (notice the "*") which meant keep executing until you can't anymore, and using an appropriate where clause it would decend tree-like structures. This feature disappeared somewhere along the way. There is I think a similar concept in SQL or SQL-99 which needs to be (re)implemented sometime, but I don't think there's an easy way right now. There was some talk on the hackers list recently about how to implement parent child comments in discussion forums, but I'm not sure if a nice solution came along. Some people seemed confident that there was a way. database@gurubase.com wrote: > > Dear all, > > I would like to define threads in message system for replies to message but if > I define too many level, I am afraid I have problem in the select... > > Say, I have define 3 levels: > > 1 > / \ > 2 3 > /\ \ > 4 5 6 > > It means message 2 is a reply to 1. > 4 is a further follow-up of 2...etc > > In table format, I would present it with > > Table A > ParentId ChildId > 1 2 > 1 3 > 2 4 > 2 5 > 3 6 > > I think I can issue the command to join table A 3 times to give the following > result > > 1st 2nd 3rd > Row1 1 2 4 > Row2 1 2 5 > Row3 1 3 6 > > But can I show > a. which level each node belongs > b. no. of child nodes it has > altogether in one "select" sql > > and if the level exceeds 3, how can I do it? > > Many thanks..... > > Best regards, > Boris
-
Re: hierarchy select question?
Craig Johannsen <cjohan@home.com> — 2000-08-03T18:02:55Z
Joe Selko's book SQL for Smarties (Morgan Kaufmann, 1995) Chapter 26 covers the topic querying and updating trees. It describes several different approaches and some vendor specific extensions for handling tree data. It mentions CJ Date's EXPLODE(<table name>) operator that would convert a table into another table with four columns: -- the level number -- the current node -- the subordinate node -- the sequence number Tree structured data is so pervasive (discussion forums, bill of materials, organization charts, software configuration, project management, file directories, etc) one would think there ought to be a lot of interest in making this easy to do in Postgres. Is anyone interested to fund some development in this area? This is likely to be a pretty big task, so if several interested parties could pool their resources, it would make it much more affordable. Cheers, Craig Chris Bitmead wrote: > Hi, > > Originally postgres had a "recursive select" to handle cases like this. > Some syntax like... > retrieve* (notice the "*") which meant keep executing until you can't > anymore, and using an > appropriate where clause it would decend tree-like structures. > > This feature disappeared somewhere along the way. There is I think a > similar concept in SQL > or SQL-99 which needs to be (re)implemented sometime, but I don't think > there's an easy way > right now. There was some talk on the hackers list recently about how to > implement parent > child comments in discussion forums, but I'm not sure if a nice solution > came along. Some > people seemed confident that there was a way. > > database@gurubase.com wrote: > > > > Dear all, > > > > I would like to define threads in message system for replies to message but if > > I define too many level, I am afraid I have problem in the select... > > > > Say, I have define 3 levels: > > > > 1 > > / \ > > 2 3 > > /\ \ > > 4 5 6 > > > > It means message 2 is a reply to 1. > > 4 is a further follow-up of 2...etc > > > > In table format, I would present it with > > > > Table A > > ParentId ChildId > > 1 2 > > 1 3 > > 2 4 > > 2 5 > > 3 6 > > > > I think I can issue the command to join table A 3 times to give the following > > result > > > > 1st 2nd 3rd > > Row1 1 2 4 > > Row2 1 2 5 > > Row3 1 3 6 > > > > But can I show > > a. which level each node belongs > > b. no. of child nodes it has > > altogether in one "select" sql > > > > and if the level exceeds 3, how can I do it? > > > > Many thanks..... > > > > Best regards, > > Boris ==================================== Craig Johannsen Critical Path Consulting, Inc. 604-762-1514 http://members.home.net/cjohan/cpath ====================================