Nested Set Model and Hierarchy Number
Posted: Sat May 19, 2007 7:20 pm
Hi All,
I'm involved on a new project where I need to implement a multi-level hierarchy category structure like:
cat_1
cat_1_1
cat_1_2
cat_1_2_1
cat_1_2_2
cat_1_3
cat_1_3_1
cat_2
cat_3
...
I'm done it using the nested set model, everything works fine, I can easily do the breadcrumb, insert a new cat, delete bunch of cats, move cats around..., only with one or two sqls. Until a new requirement comes up:
I need to display a hierarchy number next to each cat, like this:
1. cat_1
1.1 cat_1_1
1.2 cat_1_2
1.2.1 cat_1_2_1
1.2.2 cat_1_2_2
1.3 cat_1_3
1.3.1 cat_1_3_1
2. cat_2
3. cat_3
...
I have no problem building these numbers when adding the cats up, the problem is when I delete bunch of cats in different sub trees, there are many empty holes, I need to rebuild the numbers. I hope this can be done in no more than 3 sqls no matter how many cats I delete, but I fail many times.
For example, if I delete cat_1_1, cat_1_2_1,cat_1_3_1, cat_2, in the tree above, the tree will become:
1. cat_1
1.2 cat_1_2
1.2.2 cat_1_2_2
1.3 cat_1_3
3. cat_3
...
Now I wish to make it look like
1. cat_1
1.1 cat_1_2
1.1.2 cat_1_2_2
1.2 cat_1_3
2. cat_3
and I wish with hopefully 2 sqls I can achieve it, I'm having headache on this....
I'm going to have about 1000 to 2000 or even more cats looks like this in the tree, I hope the number of sqls used to rebuild these numbers won't become 1000 or 2000 ...
I can think of delete all the cats and insert them from the beginning again but looks like it takes a lot of trouble. Hopefully some update sqls can make it and the number of sqls are independent of the number of cats to delete...
With my best,
Jim
I'm involved on a new project where I need to implement a multi-level hierarchy category structure like:
cat_1
cat_1_1
cat_1_2
cat_1_2_1
cat_1_2_2
cat_1_3
cat_1_3_1
cat_2
cat_3
...
I'm done it using the nested set model, everything works fine, I can easily do the breadcrumb, insert a new cat, delete bunch of cats, move cats around..., only with one or two sqls. Until a new requirement comes up:
I need to display a hierarchy number next to each cat, like this:
1. cat_1
1.1 cat_1_1
1.2 cat_1_2
1.2.1 cat_1_2_1
1.2.2 cat_1_2_2
1.3 cat_1_3
1.3.1 cat_1_3_1
2. cat_2
3. cat_3
...
I have no problem building these numbers when adding the cats up, the problem is when I delete bunch of cats in different sub trees, there are many empty holes, I need to rebuild the numbers. I hope this can be done in no more than 3 sqls no matter how many cats I delete, but I fail many times.
For example, if I delete cat_1_1, cat_1_2_1,cat_1_3_1, cat_2, in the tree above, the tree will become:
1. cat_1
1.2 cat_1_2
1.2.2 cat_1_2_2
1.3 cat_1_3
3. cat_3
...
Now I wish to make it look like
1. cat_1
1.1 cat_1_2
1.1.2 cat_1_2_2
1.2 cat_1_3
2. cat_3
and I wish with hopefully 2 sqls I can achieve it, I'm having headache on this....
I'm going to have about 1000 to 2000 or even more cats looks like this in the tree, I hope the number of sqls used to rebuild these numbers won't become 1000 or 2000 ...
I can think of delete all the cats and insert them from the beginning again but looks like it takes a lot of trouble. Hopefully some update sqls can make it and the number of sqls are independent of the number of cats to delete...
With my best,
Jim