[ic] recursive perl subroutine

Wed Sep 4 08:42:03 2002

On Wed, Sep 04, 2002 at 09:18:41AM +0100, John Allman wrote:
> cfm@maine.com wrote:
> [snip]
> >If this is a guaranteed shallow tree, you might write it instead as an
> >SQL LEFT JOIN.  If you know it is only three levels, that will
> >be just two or three depending on your root.
> [/snip]
> 
> i'm not precicely an expert on sql either, but i'm not following you. i 
> can guarantee that its a shallow tree but i cant guarantee its depth. it 
> could be anywhere from 1 to 3 levels. i dont suppose you'd care to put 
> down an example of some sql that might be applicable? i just dont see 
> how a join would help?

It's a fairly common example in most texts on SQL.  Checking there will
help you more than any example I can give you.

FWIW, this is a brute force query that does 9 levels:
        $query=qq`SELECT
	A.rel,B.rel,C.rel,D.rel,E.rel,F.rel,G.rel,H.rel,I.rel,J.rel
	FROM relations AS A
	LEFT JOIN relations AS B ON A.rel=B.cc
	LEFT JOIN relations AS C ON B.rel=C.cc
	LEFT JOIN relations AS D ON C.rel=D.cc
	LEFT JOIN relations AS E ON D.rel=E.cc
	LEFT JOIN relations AS F ON E.rel=F.cc
	LEFT JOIN relations AS G ON F.rel=G.cc
	LEFT JOIN relations AS H ON G.rel=H.cc
	LEFT JOIN relations AS I ON H.rel=I.cc
	LEFT JOIN relations AS J ON I.rel=J.cc
	WHERE A.cc=26981 LIMIT 1`;

Relations is a simple table that ties a category to
its parent category.

rel   rel   rel   rel   rel   rel   rel   rel   rel   rel
20832 20259 0     NULL  NULL  NULL  NULL  NULL  NULL  NULL

Which means that 20832 is parent of 26981, 20259 is
parent of 20832, and 0 (top) is parent of 20259.  LEFT JOIN
gives you NULL where your structure is not as deep as the
query.  Obviously it fails for arbitrarily deep trees.

We use queries like this to precook and cache our category
hierarchies.  That's not a standard table structure for
ic, so you will have to adapt the concept.

-- 

Christopher F. Miller, Publisher                               cfm@maine.com
MaineStreet Communications, Inc           208 Portland Road, Gray, ME  04039
1.207.657.5078                                         http://www.maine.com/
Content/site management, online commerce, internet integration, Debian linux