RE: Recursive query => Nested Set

[Daevid Vincent]

I think you might consider refactoring your code to use a hybrid of Nested
Sets and Adjacency List.

It's fairly trivial to add a 'parent_id' to the nested set, so you really
don't loose any of your existing schema structure, but it will be much
faster to traverse a tree, and no recursion is required...

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Do a google search for "mysql nested set" and you will find more. Here are
some recommend:

  http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
  http://www.sitepoint.com/article/hierarchical-data-database
 
 
http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=tutorial-ferrara2
&id=3453&open=1&anc=0&view=1
  http://simon.incutio.com/archive/2003/06/19/storingTrees
  http://istherelifeafter.com/joecelko.html
  http://www.codeproject.com/cs/database/Trees_in_SQL_databases.asp

  http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html


"You had me at EHLO" --E.Webb (10.04.05)  

> -----Original Message-----
> From: Gokhan Demir [mailto:gokhan.demir@stripped] 
> Sent: Thursday, May 18, 2006 12:11 PM
> To: mysql@stripped
> Subject: RE: Recursive query
> 
> 
> After deciding a max level, you can self join the table max 
> level time.
> 
> Also, you can add two columns, one int column as a hashcode, 
> and one int
> column as showing current level. And by writing a simple 
> program, you can
> update the hashcode field. You can think bitwise. Just 
> reserve n bits for
> each level. Increment the level values by one as you traverse 
> the tree. That
> way you can algo gain the flexibility to select one branch. 
> (Calculate min
> and max hashcode values and select from table where hashcode 
> column between
> the min and max calculated values.
> 
> HTH,
> Gokhan
> 
> 
> -----Original Message-----
> From: Peter Lauri [mailto:peter@stripped] 
> Sent: Thursday, May 18, 2006 8:20 PM
> To: mysql@stripped
> Subject: Recursive query
> 
> Hi,
> 
> This is an example of an table that I store categories in. 
> Each category
> have a id and also a parent. If the parent_id is 0 that category is a
> super-category.
> 
> Assume that I would like to get a list of all categories and 
> its parent
> category, grandparents category etc down to super-category. 
> For example, if
> I take category id 13 as an example:
> 
> 13 should be chosen; because that is the one I am working with
> 9 should be chosen; because it is the parent to 13
> 6 should be chosen; because it is the parent to 9
> 1 should be chosen; because it is the parent to 6
> 
> And that is it, because 1 has parent 0, and is therefore a 
> super-category.
> 
> Right now I am doing this with PHP and a recursive function, but is it
> possible to do this directly with one query?
> 
> +----+-----------------------+----------+-----------+
> | id | name                  | priority | parent_id |
> +----+-----------------------+----------+-----------+
> |  1 | DME                   |      999 |         0 |
> |  2 | Training Material     |      999 |         0 |
> |  3 | RND                   |      999 |         0 |
> |  4 | LEAP                  |      999 |         1 |
> |  5 | TDI                   |      999 |         1 |
> |  6 | Technical Support     |      999 |         1 |
> |  7 | Training Module       |      999 |         1 |
> |  8 | Detail by Component   |      999 |         6 |
> |  9 | Step/Process          |      999 |         6 |
> | 10 | Assessment            |      999 |         9 |
> | 11 | Design                |      999 |         9 |
> | 12 | Implement and Monitor |      999 |         9 |
> | 13 | Evaluation            |      999 |         9 |
> | 14 | Reflection            |      999 |         9 |
> | 15 | Transition            |      999 |         9 |
> | 16 | TDI                   |      999 |         2 |
> | 17 | LEAP                  |      999 |         2 |
> | 18 | Other                 |      999 |         2 |
> | 19 | Tools                 |      999 |         3 |
> | 20 | R&D Document          |      999 |         3 |
> +----+-----------------------+----------+-----------+
> 
> Best regards,
> Peter Lauri
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 
>