Re: tree structure photo gallery date quiery - Mailing list pgsql-sql

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