pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedget branches & childs from database
2+ messages / 2 participants
[nested] [flat]
* get branches & childs from database
@ 2004-12-16 21:23 Marcus Krause <marcus.k79@expires-2004-12-31.arcornews.de>
0 siblings, 1 reply; 2+ messages in thread
From: Marcus Krause @ 2004-12-16 21:23 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: get branches & childs from database
@ 2004-12-18 09:26 Michael Fuhr <mike@fuhr.org>
parent: Marcus Krause <marcus.k79@expires-2004-12-31.arcornews.de>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2004-12-18 09:26 UTC (permalink / raw)
To: Marcus Krause <marcus.k79@expires-2004-12-31.arcornews.de>; +Cc: pgsql-sql
On Thu, Dec 16, 2004 at 10:23:23PM +0100, Marcus Krause wrote:
> I'm trying to build up a menu by sending ONE query to database.
See the contrib/ltree module in the PostgreSQL source, or in
whatever package you must install to get the contributed modules.
ltree provides easy ways to query tree-like structures.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-12-18 09:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-16 21:23 get branches & childs from database Marcus Krause <marcus.k79@expires-2004-12-31.arcornews.de>
2004-12-18 09:26 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox