From: Oleg Bartunov <oleg@sai.msu.su>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: sad <sad@bankir.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: tree structure photo gallery date quiery
Date: Wed, 17 Nov 2004 11:04:18 +0300 (MSK)
Message-ID: <Pine.GSO.4.61.0411171102460.9952@ra.sai.msu.su> (raw)
In-Reply-To: <200411170035.50762.gary.stainburn@ringways.co.uk>
References: <200411161129.46131.gary.stainburn@ringways.co.uk>
<200411161608.18040.sad@bankir.ru>
<200411170035.50762.gary.stainburn@ringways.co.uk>
Gary,
if you need really fast solution for you task and dont't afraid
non-standard soltion, take a look on contrib/ltree module.
http://www.sai.msu.su/~megera/postgres/gist/ltree/
Oleg
On Wed, 17 Nov 2004, Gary Stainburn wrote:
> 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';
>
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
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: oleg@sai.msu.su, gary.stainburn@ringways.co.uk, sad@bankir.ru
Subject: Re: tree structure photo gallery date quiery
In-Reply-To: <Pine.GSO.4.61.0411171102460.9952@ra.sai.msu.su>
* 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