From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: sad <sad@bankir.ru>
To: pgsql-sql@postgresql.org
Subject: Re: tree structure photo gallery date quiery
Date: Wed, 17 Nov 2004 00:35:50 +0000
Message-ID: <200411170035.50762.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <200411161608.18040.sad@bankir.ru>
References: <200411161129.46131.gary.stainburn@ringways.co.uk>
<200411161608.18040.sad@bankir.ru>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk, sad@bankir.ru
Subject: Re: tree structure photo gallery date quiery
In-Reply-To: <200411170035.50762.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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