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
>
>
> ************
>
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