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 |
NYMR3 |  10 |  13 |  3 |      1 | Middleton 4 |   3 |   4 |  4 |      2 | Steam Gala 5 |   5 |   6 |  5 |      2 |
DieselGala 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
betweenp2.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