pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrecusrion
2+ messages / 2 participants
[nested] [flat]
* recusrion
@ 1999-11-27 23:00 Julien Cadiou <julienc@vicnet.net.au>
0 siblings, 1 reply; 2+ messages in thread
From: Julien Cadiou @ 1999-11-27 23:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] recusrion
@ 1999-11-29 09:38 Stoyan Genov <genov@digsys.bg>
parent: Julien Cadiou <julienc@vicnet.net.au>
0 siblings, 0 replies; 2+ messages in thread
From: Stoyan Genov @ 1999-11-29 09:38 UTC (permalink / raw)
To: Julien Cadiou <julienc@vicnet.net.au>; +Cc: 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.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
>
>
> ************
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-11-29 09:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-27 23:00 recusrion Julien Cadiou <julienc@vicnet.net.au>
1999-11-29 09:38 ` Stoyan Genov <genov@digsys.bg>
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