Skip site navigation (1) Skip section navigation (2)

get branches & childs from database

From: Marcus Krause <marcus(dot)k79(at)expires-2004-12-31(dot)arcornews(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: get branches & childs from database
Date: 2004-12-16 21:23:23
Message-ID: cpsuc5$6sl$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-sql
Hello newsgroup!

I'm trying to build up a menu by sending ONE query to database.
Afterwards the result is used by PEAR::HTML_Menu get
a html-structered menu.

db-structure of gallery:
+----+-------+------+------+--------+-------+
| id | title | date | root | parent | level |
+----+-------+------+------+--------+-------+
| 5  | A     |  XX  |  5   |   0    |   1   |
| 1  | A2    |  XX  |  5   |   5    |   2   |
| 2  | A1    |  XX  |  5   |   5    |   2   |
| 3  | A11   |  XX  |  5   |   2    |   3   |
| 4  | A12   |  XX  |  5   |   2    |   3   |
| 6  | A21   |  XX  |  5   |   1    |   3   |
| 7  | A211  |  XX  |  5   |   6    |   4   |
| 8  | B     |  XX  |  8   |   0    |   1   |
| 9  | B1    |  XX  |  8   |   8    |   2   |
+----+-------+------+------+--------+-------+

following limitations are set:
- for root-nodes parent=0 have to be set
- result should by available after sending ONE query
- child of the specified gallery should be delivered
- branch of the specified gallery should be delivered

menu-structure after the query for id=6:

+ A
|-> A1        (optional, no need to be but nice to have)
|-> A2
   |-> A21    (specified id)
      |-> A211
+ B


Therefore I need the following result after sending the query:

+----+-------+--------+
| id | title | parent |
+----+-------+--------+
| 8  | B     |   0    |
| 6  | A21   |   1    |
| 7  | A211  |   6    |
| 1  | A2    |   5    |
| 5  | A     |   0    |
| 2  | A1    |   5    | (optional)
+----+-------+--------+

I currently use following query:

SELECT id,title,parent,FROM gallery
WHERE (id=root OR root IN
      (SELECT root FROM gallery WHERE id=6))
AND gall_level <= (SELECT level FROM gallery WHERE id=6)+1
ORDER BY level ASC,date DESC

With this query I get the additional branch under A1, but that's not
what I want. I don't know what to do.
I'm able to use views, subselects, rules (of course) and furthermore
functions (plpgsql).

I'd be pleased, if there are any ideas

Thanks, Marcus.

Responses

pgsql-sql by date

Next:From: YudieDate: 2004-12-16 23:53:43
Subject: plpgsql.. SELECT INTO ... WHERE FIELD LIKE
Previous:From: Andreas KretschmerDate: 2004-12-16 14:15:57
Subject: Re: [despammed] question about index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group