English is not my native language so I applogize for bad grammar and spelling...

I am using LLBLGen Pro 2.0 in a project where i need to fetch a 'tree' from a database representing the different organizational units of an organization.

The table is structured as follows:

DepartmentID | ParentDeptartmentID | DepartmentName | other fields which are not important to this example....

There is no limit regarding the depth of the tree so there is no way I can think of for me to solve this using some sort of recursion. I have browsed through the forums and I assume that the solution is to use prefetch paths but I am having trouble understanding exactly how.

I am using VB.NET 1.1 and LLBLGen Pro 2.0 - Adapter scenario.

I am very grateful for any help I can get.

I have had to solve this as well as others. I realized there were several solutions.

My solution was to have every node in the tree have an additional field for the root of the tree, then I simply fetch all nodes with the root (including the root). Then I simply "build" the ordered tree from that collection which is an O(N) operation if done right. Forgive me for not giving code example, I can't find the link I used for reference.

The other solution if you don't care about performance would be to write a recursive routine to "walk the tree" starting at the root. This will of course do N queries (eek!).

I'd be very interested in the link also, as I'm just about to start work on a PageBuilder that would require a similar configuration. Am currently looking into nested sets et al. but to be honest it makes my brain hurt. :S

Cheers,

Pete

It comes down to this (very short description) you have a hashtable or dictionary, in which you store id-node combinations. You then traverse the set of entities from front to back. for each entity you create a new node and add it to the hashtable. You then use the hashtable to select the parent for the entity and add the node to the parent's node (which you obtain from the hashtable!)'s nodelist.

viola

MatthewM wrote:

I have had to solve this as well as others. I realized there were several solutions.

My solution was to have every node in the tree have an additional field for the root of the tree, then I simply fetch all nodes with the root (including the root). Then I simply "build" the ordered tree from that collection which is an O(N) operation if done right. Forgive me for not giving code example, I can't find the link I used for reference.

The other solution if you don't care about performance would be to write a recursive routine to "walk the tree" starting at the root. This will of course do N queries (eek!).

Since the tree is quite small and performance isnt that much of an issue I decided to go with recursion for this one. Might not be the ideal solution but it works.

@everyone: Thanks for the help and ideas.

Otis wrote:

It comes down to this (very short description) you have a hashtable or dictionary, in which you store id-node combinations. You then traverse the set of entities from front to back. for each entity you create a new node and add it to the hashtable. You then use the hashtable to select the parent for the entity and add the node to the parent's node (which you obtain from the hashtable!)'s nodelist.

viola

I'm looking at a similar scenario here as well. Fortunately I'm using SQL 2005 at the back-end so I can use a CTE to do the recursion for me

Basic solution goes something like this:

1. Create a CTE that joins from the parent dept id to the dept id
2. Select just the id of the dept table (or whatever you might be using) from the CTE
3. Wrap that in a stored procedure
4. Call the stored procedure to get a list of dept ids
5. Fetch your entities by using a FieldCompareRangePredicate, giving it the list of ids

Hey Presto - two hits to the database and away you go.

Now, if you need to reconstruct the hierarchy in code, you can also apply a sort to the CTE to make sure you get the nodes in a sensible order (i.e. starting at the root of the tree and working down) then when you do the fetch you should always (but best to ensure in code!) that you have already got the parent available to you.

Do what Otis suggested (id + node in a dictionary) to make sure you can reconstruct the tree easily.

The benefit of doing it this way is that

a) You don't need to track the root node in the database - which makes updates that bit easier b) It's dead easy to return partial trees - just tell it what node you want to use as the root, and the CTE will get the bits from there down

Obviously, this only works if your DB supports a CTE-type construct. Oracle does as well (and others, presumably) but it's so long since I used it that i can't remember what it's called...

And, there are myriad discussion on t'internet about which is the better data model to go with. Most that I've seen actually favour the 'store the root node for each node' approach, but somehow i just think that is less elegant. Each to their own, I guess.

Oh, the joys of SQL 2005