Skip site navigation (1) Skip section navigation (2)

Re: tree structure photo gallery date quiery

From: Rod Taylor <pg(at)rbt(dot)ca>
To: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: tree structure photo gallery date quiery
Date: 2004-11-16 13:23:23
Message-ID: 1100611403.99341.21.camel@home (view raw or flat)
Thread:
Lists: pgsql-sql
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.

-- 


In response to

pgsql-sql by date

Next:From: Bruno PrévostDate: 2004-11-16 14:04:26
Subject: Table definition
Previous:From: sadDate: 2004-11-16 13:08:17
Subject: Re: tree structure photo gallery date quiery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group