I'm working on a hierarchy builder script that manipulates a categories table in a database. in the table, each category has a name, id, parent_id, depth, and order_id
The parent_id is to link up the categories in a tree-like structure, and the depth is an int representing how many layers over to the right it is, i use it to generate the tree branch graphics.
What I'm trying to do is create an order_id for each item that represents exactly where in the tree it goes. that way i can just "select * from categories order by order_id" and then use the depth to nudge them over if i want to display the tree.
why orderid? because 1) i dont want to do a gnarly set of sql queries based on the structure, and 2) i have too many categories to select all and then do a recursive sort based on the id/parent_id relationships, it takes too long.
I've been assigning the order_id's a number, just counting up. What I want to do is write a function that generates the orderid for inserts and edites to the tree structure.
therefore, if the category structure looks like this:
cat A orderid=1
cat B orderid=2
cat C orderid = 3
then say i want to insert a cat between a and b, the orderid would be 1.1:
cat A orderid=1
cat d orderid = 1.1
cat b orderid=2
cat c orderid = 3
then if i want another one right after that, the orderid would be 1.11, etc:
cat A orderid=1
cat d orderid = 1.1
cat e orderid = 1.11
cat b orderid=2
cat c orderid = 3
but now lets say i want one between d and e. then that orderid would be 1.101:
cat A orderid=1
cat d orderid = 1.1
cat f orderid = 1.101
cat e orderid = 1.11
cat b orderid=2
cat c orderid = 3
make sense? so what i can do is just write a function to hit the database and figure out the most logical orderid and then that's that
as you can see this seems kind of convoluted!! (but it works) the orderid can be crazy long, it's just a string (not a float). and then i can run a script periodically to refresh them if i want with new whole numbers.
i was just wondering what you all thought of the idea and if there are any suggestions for how to do it better. is there a slicker way to do it?
thanks
rdrnnr
