Re: tree structure photo gallery date quiery

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