Page 1 of 1

Nested Set Model and Hierarchy Number

Posted: Sat May 19, 2007 7:20 pm
by jimthunderbird
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

Posted: Sat May 19, 2007 8:27 pm
by Begby
Where are you getting the hierarchy number from? If you are storing it in the category name, that is bad.

Anything value that is dependent on other records, such as a sum, calculation, or this hierarchy number, should be calculated. So you should instead find a way to generate that number instead of looking for a way to update the number whenever you add/delete records.

Posted: Sun May 20, 2007 12:21 am
by jimthunderbird
Hi,
I stored the hierarchy number in a field named hierarchy.
Since the cat tree will be very large, I'm thinking of doing a paging, each page has 20-30 cats, each with the hierarchy number displayed.
I do think of a php way to calculate the hierarchy number, but looks like I need to calculate all of them at once first and then I can display them page by page, which consumes a lot of memory.
I try to think of a way to calculate them page by page by fail. Any good ideas?

With my best,
Jim