From: Rod Taylor <pg@rbt.ca>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: tree structure photo gallery date quiery
Date: Tue, 16 Nov 2004 08:23:23 -0500
Message-ID: <1100611403.99341.21.camel@home> (raw)
In-Reply-To: <200411161129.46131.gary.stainburn@ringways.co.uk>
References: <200411161129.46131.gary.stainburn@ringways.co.uk>
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.
--
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: pg@rbt.ca, gary.stainburn@ringways.co.uk
Subject: Re: tree structure photo gallery date quiery
In-Reply-To: <1100611403.99341.21.camel@home>
* 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