From: Date: August 30 2005 10:40pm
Subject: Re: hierarchical db/depth?
List-Archive: http://lists.mysql.com/mysql/188482
Message-Id: <4314C431.4010609@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mel,

 >I would like to know if there is a way to have a kind of "auto-extension"
 >of the query if there is for example a fifth level?

SQLdoesn't have recursion, so to avoid writing literal queries for each 
number of levels you need either (i) something like an edge list, an 
adjacency list, or a preorder tree traversal ('nested sets' as Celko 
calls them) model, or (ii) write a query generator in an app language.

PB

-----

mel list_php wrote:

> hi!
> I'm still trying to organize an hierachical db (I saw the last article 
> on mysql.com: 
> http://dev.mysql.com/tech-resources/articles/hierarchical-data.hml).
> I began working with what the author calls "adjency list model" (I 
> think the nested set model is a bit too complex for what I want to 
> do.In addition I found an equivalent schema in an other application 
> which is close to mine....and working fine)
> Basically, I have a table term (term_id, name), relation 
> (relation_id,term_id1,term_id2,type_id) and a last table to identify 
> the relations type id, relation_type (type_id,type_name).
>
> I can retrieve all the info I need with that query:
> SELECT t1.term_id2 AS lev0, term0.name AS parent0, trel1.type_name AS 
> rel1, t1.term_id1 AS lev1, term1.name AS parent1, trel2.type_name AS 
> rel2, t2.term_id1 AS lev2, term2.name AS parent2, trel3.type_name AS 
> rel3, t3.term_id1 AS lev3, term3.name AS parent3
> FROM relation AS t1
> LEFT JOIN relation_type AS trel1
> USING ( type_id )
> LEFT JOIN term AS term0 ON ( t1.term_id2 = term0.term_id )
> LEFT JOIN relation AS t2 ON t2.term_id2 = t1.term_id1
> LEFT JOIN relation_type AS trel2
> USING ( type_id )
> LEFT JOIN term AS term1 ON ( t2.term_id2 = term1.term_id )
> LEFT JOIN relation AS t3 ON t3.term_id2 = t2.term_id1
> LEFT JOIN relation_type AS trel3
> USING ( type_id )
> LEFT JOIN term AS term2 ON ( t3.term_id2 = term2.term_id )
> LEFT JOIN term AS term3 ON t3.term_id1 = term3.term_id
> WHERE t1.term_id2 =1
>
> It's just adding a new sub-part to the query for each level.
> This is working fine on my data sample, because I know that I have 
> exactly 4 levels of depth.
> I would like to know if there is a way to have a kind of 
> "auto-extension" of the query if there is for example a fifth level?
> Thanks for any help or tip,
> melanie
>
> _________________________________________________________________
> Be the first to hear what's new at MSN - sign up to our free 
> newsletters! http://www.msn.co.uk/newsletters
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.16/83 - Release Date: 8/26/2005