1. Home
  2. LLBLGen Pro
  3. Architecture

Hierarchies by self join

Posts   
 
    
KelMan
User
Posts: 11
Joined: 25-Oct-2006
# Posted on: 28-Oct-2006 11:55:36   

Hello, yet another question...

Is there a way to get a hierarchy that is formulated by self join (like the EmployeeID->ReportsTo on Employees of Northwind) or do I have to generate it by using a loop?

I'm talking about using the Adapter template.

Thanx, Manos

Chester
Support Team
Posts: 223
Joined: 15-Jul-2005
# Posted on: 28-Oct-2006 21:13:17   

KelMan wrote:

Hello, yet another question...

Is there a way to get a hierarchy that is formulated by self join (like the EmployeeID->ReportsTo on Employees of Northwind) or do I have to generate it by using a loop?

I'm talking about using the Adapter template.

Thanx, Manos

There are many different strategies for populating hierarchies, but the most common that I've run across is using recursion. It depends a lot on how big the tree or graph is, and how it will be used. So some questions for you to answer then are:

How many records (total) exist in this table that has the recursive relationship? How will you present the data, and for what purpose? e.g. a tree that allows you to select a member of the hierarchy? What technologies are you using at the presentation tier? e.g. ASP.NET/HTML, WinForms

KelMan
User
Posts: 11
Joined: 25-Oct-2006
# Posted on: 29-Oct-2006 08:59:01   

Actualy, I was hoping that there was a way to get the hierarchy without having to implement the recursion myself simple_smile Like, say, by defining a recursive SubPath on a prefetch path and using a predicate to define when to stop the recusion. That is, have the system do the dirty work for me... smile

sami
User
Posts: 93
Joined: 28-Oct-2005
# Posted on: 07-Nov-2006 13:34:35   

Also much depends of how you have implemented the hierarchy in database. Adjacency model (the one you are referring to where child know his parent) is one, and then there is the nested set model. You also might store the hiearchy path (lineage).

Using nested sets, you can retrieve all subelements with single sql query, but it has it own difficulties also.

Creating predicates for nested set table is another topic, but atleast the more simple queries are doable. Here is a thread about that

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5880&HighLight=1

But for example, querying immediate childs only requires use of a rather complex subquery, and I am not sure if it is possible to do with llblgen predicates.