Hierarchy Query Help

netski · **Joined:** Thu Mar 30, 2006 2:10 pm **Posts:** 14

NHibernate Version: 2.0.1GA

Hello,

I'm wondering if anyone could help me with the following scenario. I have a Employee class that has the following definition:

Code:

public class Employee
{
    public Employee ReportsTo { get; set; }
    public string Name { get; set; }
    public string Role { get; set; }
}

And the following employee structure:

Code:

Name: John Doe Role: President
`- Name: Jane Doe Role: Vice President
     `- Name: Simon Role: Managing Director
          `- Name: Bob Role: Sales Manager
              `- Name: Michael Role: Sales Rep
          `- Name: Ruth Role: Product Manager

I would like to create a criteria where I could pass the Employee object for John Doe (President) and retrieve all the Employees including Michael (Sales Rep).

Or in the event that I pass the Employee object for Jane Doe (Vice President) that I retrieve all the Employees excluding John Doe (President).

Any suggestions? Possible using ICriteria?

(I'm looking for suggestions, I do not have an hbm created)

Cheers,
Timothy Grant Vogelsang

netski · **Joined:** Thu Mar 30, 2006 2:10 pm **Posts:** 14

Anyone?

wolli · **Posted:** Tue Jun 16, 2009 12:59 am

I'm using a SQL query in a similar scenario. Write the SQL you need fot that and wrap it in a hibernate SQL query.

netski · **Joined:** Thu Mar 30, 2006 2:10 pm **Posts:** 14

Hello wolli, what does your query look like?

Cheers

wolli · **Posted:** Wed Jun 17, 2009 2:34 am

Code:

With ProjectTree( uid ) as
(
    select p.uid from sx.bus_project p where parentuid = :uid
    union all
    select p.uid from sx.bus_project p inner join ProjectTree t on p.parentuid = t.uid
)
select uid from ProjectTree

Have a look here for hierarchical queries:

http://msdn.microsoft.com/en-us/library/ms186243.aspx