Subject: Re: tree structure photo gallery date quiery
From: Rod Taylor <pg@rbt.ca>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200411161129.46131.gary.stainburn@ringways.co.uk>
References: <200411161129.46131.gary.stainburn@ringways.co.uk>
Content-Type: text/plain
Date: Tue, 16 Nov 2004 08:23:23 -0500
Message-Id: <1100611403.99341.21.camel@home>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2004-11-16 at 11:29 +0000, Gary Stainburn wrote:
> 
> 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?

There isn't a very simple answer to that question because you don't have
enough information. To make that view, you require there to be a maximum
depth to the galleries (say 3 galleries deep only -- including root) OR
you need another structure which represents the relationship between all
of the galleries.

For the latter, something like gallery_lookup(id, cid, nest_depth):

1  1  0
2  2  0
3  3  0
4  4  0
5  5  0
6  6  0
7  7  0
1  2  1
1  3  1
1  4  2
1  5  2
1  6  2
1  7  2
2  4  1
2  5  1
2  6  1
3  7  1

Now that you know the relationship between them all, you can quickly and
easily determine all galleries underneath the top level one. Sorry,
don't know the technical term, if there is one, for this operation.

Now lets make a pair of views:

CREATE VIEW gallery_aggregate 
AS SELECT id, name,
		sum(CASE WHEN pid IS NULL THEN 0 ELSE 1 END) AS photocount,
		max(added) AS max_added
	FROM gallery
	LEFT OUTER JOIN photos USING (id)
 GROUP BY id, name;

CREATE VIEW gallery_view_you_want
AS SELECT name,
		sum(photocount),
		max(max_added)
	FROM gallery
	JOIN gallery_lookup AS gl USING (id)
       JOIN gallery_aggregate AS ga ON (gl.cid = ga.id)
 GROUP BY name;


There are plenty of steps you can take to make this both faster and/or
use less storage; optimize aggregates, use a function to calculate the
'gallery_lookup' contents, etc.

None of this has been tested.

--