Re: tree structure photo gallery date quiery

Gary,

If you're not to worried about tying yourself to Postgres and you're
sure you want to create a tree structure, you may want to check out
the ltree contrib module. It will allow you to create an index over
the entire tree, and will allow you to use real names instead of INTs
for the nodes in the tree. ltree will also allow you to have one
particular node at different points in the tree.

On Wed, 17 Nov 2004 00:35:50 +0000, Gary Stainburn
<gary(dot)stainburn(at)ringways(dot)co(dot)uk> 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';
>
>
>
> --
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer