On Tuesday 16 November 2004 1:08 pm, sad wrote: > On Tuesday 16 November 2004 14:29, Gary Stainburn wrote: > > Hi folks. > > > > I'm looking at the possibility of implementing a photo gallery for > > my web site with a tree structure > > > > How would I go about creating a view to show a) the number of > > photos in a gallery and b) the timestamp of the most recent > > addition for a gallery, so that it interrogates all sub-galleries? > > nested-tree helps you > associate a numeric interval [l,r] with each record of a tree > and let father interval include all its children intervals > and brother intervals never intersect > > see the article http://sf.net/projects/redundantdb > for detailed examples and templates
Hi Sad, I had actually started working on this because I found an old list posting archived on the net at http://www.net-one.de/~ks/WOoK/recursive-select. As you can see below, I've got the tree structure working and can select both a node's superiors and it's subordinates. Using these I can also find a node's last added date and photo count. However, I've got two problems. Firstly, below I've got the two example selects for listing owners and owned nodes. I can't work out how to convert these two parameterised selects into views. Secondly, in order to get the results shown here, I've had to write two seperate but similar pl/pgsql functions to return the photo_count and photo_updated columns, which result in 2 * select per call * twice per line * 7 lines = 28 selects Is there a more efficient way? nymr=# select *, photo_count(id), photo_updated(id) from gallery; id | parent | name | photo_count | photo_updated ----+--------+--------------------+-------------+------------------------ 1 | 0 | Root | 4 | 2004-11-10 12:12:00+00 2 | 1 | NYMR | 3 | 2004-11-10 12:12:00+00 3 | 1 | Middleton | 1 | 2004-01-01 09:12:12+00 4 | 2 | Steam Gala | 2 | 2004-11-10 12:12:00+00 5 | 2 | Diesel Gala | 1 | 2004-10-01 10:00:00+01 6 | 2 | From The Footplate | 0 | 7 | 3 | From The Footplate | 1 | 2004-01-01 09:12:12+00 (7 rows) Below is everything I have so far, including one of the functions I'm using: create table gallery ( id serial, parent int4, name varchar(40), primary key (id)); create table photos ( pid serial, id int4 references gallery not null, added timestamp, pfile varchar(128) not null, pdesc varchar(40) not null, primary key (pid)); create table tree ( -- seperate for now to ease development id int4 references gallery not null, lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), CONSTRAINT order_okay CHECK (lft < rgt) ); copy "gallery" from stdin; 1 0 Root 2 1 NYMR 3 1 Middleton 4 2 Steam Gala 5 2 Diesel Gala 6 2 From The Footplate 7 3 From The Footplate \. copy "photos" from stdin; 1 4 2004-11-10 10:10:00 80135-1.jpg 80135 light-engine 2 4 2004-11-10 12:12:00 6619-1.jpg 6619 on-shed 3 5 2004-10-01 10:00:00 D7628.jpg Sybilla 4 7 2004-01-01 09:12:12 mm-21.jpg No. 4 Mathew Murrey \. copy "tree" from stdin; 1 1 14 2 2 9 3 10 13 4 3 4 5 5 6 6 7 8 7 11 12 \. -- select leaf and parents -- want to convert to a view so I can type something like -- 'select * from root_path where id = 7; nymr=# select p2.id, g.parent, g.name from gallery g, tree p1, tree p2 where g.id = p2.id and p1.lft between p2.lft and p2.rgt and p1.id = 7; id | parent | name ----+--------+-------------------- 1 | 0 | Root 3 | 1 | Middleton 7 | 3 | From The Footplate (3 rows) -- Select parent and subordinates - also want to convert to view nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1; id | lft | rgt | id | parent | name ----+-----+-----+----+--------+-------------------- 1 | 1 | 14 | 1 | 0 | Root 2 | 2 | 9 | 2 | 1 | NYMR 3 | 10 | 13 | 3 | 1 | Middleton 4 | 3 | 4 | 4 | 2 | Steam Gala 5 | 5 | 6 | 5 | 2 | Diesel Gala 6 | 7 | 8 | 6 | 2 | From The Footplate 7 | 11 | 12 | 7 | 3 | From The Footplate (7 rows) -- use the one above to select photos - another view nymr=# select count(pid), max(added) from photos where id in ( nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft and p2.rgt and p2.id = 1 nymr(# ); count | max -------+------------------------ 4 | 2004-11-10 12:12:00+00 (1 row) nymr=# select count(pid), max(added) from photos where id in ( nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft and p2.rgt and p2.id = 2 nymr(# ); count | max -------+------------------------ 3 | 2004-11-10 12:12:00+00 (1 row) nymr=# select count(pid), max(added) from photos where id in ( nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft and p2.rgt and p2.id = 3 nymr(# ); count | max -------+------------------------ 1 | 2004-01-01 09:12:12+00 (1 row) Here is the photo_count function, photo_updates just has differnt attribute names/types create function photo_count(int4) returns int4 as 'DECLARE gallery_id alias for $1; pcount int4; begin select count(pid) into pcount from photos where id in ( select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft and p2.rgt and p2.id = gallery_id ); return pcount; end' language 'plpgsql'; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
