Re: [SQL] recusrion - Mailing list pgsql-sql

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.nameFROM classes a, classes b, classes cWHERE 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
> 
> 
> ************
>