pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stoyan Genov <genov@digsys.bg>
To: Julien Cadiou <julienc@vicnet.net.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] recusrion
Date: Mon, 29 Nov 1999 11:38:08 +0200
Message-ID: <199911290938.LAA00594@lorna.digsys.bg> (raw)
In-Reply-To: <3.0.3.32.19991128100041.0155db00@mail.vicnet.net.au>

Hi,
IMHO, the SELECT goes like this (suppose you have 3 levels in your hierarchy
and your table is named "classes"):

	SELECT a.name, b.name, c.name
	FROM classes a, classes b, classes c
	WHERE a.owner = 0 AND b.owner = a.id AND c.owner = b.owner;

I have such a table in one of the databases. The things work, but I think 
there are
a couple of drawbacks:
	1) if you decide to change the hierarchy level (i.e., decide to have 4 
levels in the
classification), you have to change all your software from the db level up 
to the top;
	2) you have to scan through all the levels (because they ARE in one table) 
in
order to get to, let's say, only one level of the classification. When the 
table grows enough,
the SELECT will be killingly slow.

So, if the stage of the project allows, change the database and use a 
separate table for each
level of the classification. Thus, you will have no problems with drawback 
2) and the prob-
lems with drawback 1) will be much easier to solve. If you continue using 
perl (I do, and
what I am offering now works in another project) you can name the tables 
from the
different levels regularly (i.e., level0 for the first , level1 for the 
second and so on) you can
very easy control the level by a parameter given by the upper layer of your 
software).

This is it. I hope it helps.

Stoyan Genov



> Hi,
> 
> I'm doing a portal and have a recursion problem.
> I just need advice on whether or not I'm wasting my time in thinking I can
> do what I need with SQL.
> We're basically building a yahoo-like portal. My categories table is as
> follows this email.
> Each  category has a primary key and an owner (the owner is the primary key
> of the category owning that category). I want to extract a site map in one
> query: ie: extract the cetagory and its subcategories if any and any of the
> subcategories' subcategories if any etc ....
> Right now, in failure to do so with SQL, I'm selecting the whole thing in a
> hash array in perl and reorganising it (which is fine, it worksm but if it
> can be "cleaner", that's better !), but I was wondering if I should
> continue looking for the SQL answer ... I've seen similar things done, but
> somehow it's slightly different to this case and I can't seem to see it !
> I've written a few functions that lead me nowhere, performed self joins etc
> ... but I think I'm looking at it from the wrong angle ... any suggestions ?
> Thanks.
> 
> id|owner|name
> --+-----+-------------------------
>  2|    0|Business
>  9|    7|How to learn
> 12|    9|Good schools
> 13|   12|Good schools in Melbourne
> 14|   13|Good schools in Carlton
> 16|    6|Victorian Private Banks
> 17|    1|Barbeques
> 18|   17|Victorian barbeques
>  3|    1|Sports
>  4|    2|Banking
>  5|    3|Tennis
>  6|    4|Victorian Banks
>  7|    5|Lessons
>  8|    2|Finance
>  1|    0|Outdoors
>  0|    0|Home
> 19|    5|Tennis Clubs
> 20|   19|Melbourne CLubs
> 21|    5|Tournaments
> 22|   13|Free tuition
> 
> 
> ************
> 




view thread (2+ messages)

Message-ID: <199911290938.LAA00594@lorna.digsys.bg>
Permalink:  ../199911290938.LAA00594@lorna.digsys.bg/
Also on:    postgresql.org/message-id/199911290938.LAA00594@lorna.digsys.bg

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: genov@digsys.bg, julienc@vicnet.net.au
  Subject: Re: [SQL] recusrion
  In-Reply-To: <199911290938.LAA00594@lorna.digsys.bg>
  References: <3.0.3.32.19991128100041.0155db00@mail.vicnet.net.au> <199911290938.LAA00594@lorna.digsys.bg>

* 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