SanjayK wrote:
This path is a nice idea to keep it simple for what I want to do. But I have
a question:
Suppose the database is like this:
id, parentid, data
1, 0, parent
2, 1, son B
3, 1, son A
4, 1, daughter A
5, 1, daughter B
6, 2, grandchild B
7, 2, grandchild A
Using the path approach, how can I sort this tree such that the structure
remains same (children immediately after parent) but the children are sorted
in their own level. So the result should be:
id, parentid, data
1, 0, parent
4, 1, daughter A
5, 1, daughter B
3, 1, son A
2, 1, son B
7, 2, grandchild A
6, 2, grandchild B
If I sort using the path then the structure is disturbed because the top
level children are then clubbed together (same path) and their own children
are separated by several rows downwards.
The reason I want to preserve the structure is that I am filling a tree
control and preserving the structure makes it simpler to code in a recursive
manner.
Sanjay,
I'm not sure I follow your question. You cant display an arbitrary tree
so that all children are immediately after their parent. The closest you
could come would be a traversal something like this.
P1
C1
GC1
C2
GC2
GC3
C3
P2
C4
C5
GC4
C6
P3
But in this case, the children of P1 do not follow immediately after P1,
the grandchildren get in the way.
If you only want one level, i.e. the children of a node, don't use the
path. Simply select all the nodes with the parent_id equal to the id of
the node in question.
This is what I do when filling a tree control. I only retrieve the
children when the user expands a node in the tree control. This makes
displaying the tree control very fast since you only need to get the
root nodes first. Then you add nodes to the tree control as the user
expands nodes.
To get the display above the user would initially see the three root nodes
P1
P2
P3
After they expand P1 they would see
P1
C1
C2
C3
P2
P3
If they continued to expand all nodes they would get your ordered
display. You will have done six simple queries, one for each expand
click the user has made.
Actually, there is one small complicating factor, and that is that you
must also determine if you should show an expand control for each node
in your tree control. You only show the expand control if the node has
children. So simply check if each node appears in the parent_id column
of the tree table.
select exists (select id where parent_id = :node_id);
This query will work, but it requires a full table scan to check for the
existence of children. The speed of this query can be greatly improved
by adding an index on the parent_id column.
create index tree_parent on tree(parent_id);
Now this query goes directly to the first child node if present. It
still finds all the children, which may be a concern for a tree with a
high fanout. This can be eliminated by changing the query to only check
for the existence of one child.
select exists (select id where parent_id = :node_id limit 1);
If this query returns true, you add an expand control to the item in the
tree control.
I have also used this path method with large static trees, where I
create a temporary table that stores the count of children for each
node. Then I join this table with the tree to check if I should add the
expand control to a tree item.
HTH
Dennis Cote
