Re: using a self referencing table

drfrog(at)smartt(dot)com wrote:

> i have a table with this structure
>
> name (varchar)|category id (int4)|parent category id (int4)|leaf node
> (bool)
>
> im trying to make a perl script that should tree the info
>
> parent cat
> subcat
> subcat2
> subcat2
> subcat
> subcat2
> ...
>
> but im having troubles wrapping my head around this
>
> im using the Pg modules to access postgres
>
> if anyone has any suggestions please lemme know thanks!

The trick is to be able to sort all the elements of the hierarchy so
that they come out in the right order, that is, grouped by parent, and
then indent them.

You can do this with two extra redundant fields, clevel int and csort
text. Every time you insert a node in the tree, include the calculated
level of the node (i.e. clevel = parent->clevel + 1) and a string which
ensures that the children of a given node are grouped together (i.e.
csort = parent->csort + category id::text). Probably best to left pad
the category id with an appropriate number of zeroes for this
calculation.

When you retrieve the nodes, order by csort, and convert clevel into the
appropriate number of indents.