Showing posts with label CTE. Show all posts
Showing posts with label CTE. Show all posts

Sunday, December 23, 2012

Using NHibernate to fetch Hierarchical information with SqlServer - Part - 3

In the previous post we saw how we can make use of Common Table Expression (CTE) and NHibernate to fetch Hierarchical information with SqlServer.  In this post we will see one more way in which we can map the CTE with NHibernate and this will be a more interesting way of mapping the CTE with NHibernate.

Just a small recap of the problem that we are trying to solve:

The Problem:

One Manager can have many subordinates (employees working under him) and one employee can have many managers

In the previous post we had defined a function that would take one argument, i.e. the ManagerId, this function would return a list of all the Sub-accounts associated with the manager at any level.  The function looked like
Then we mapped this function using a named query, but that's a little boring way of doing stuff.  Moreover, if we go that path then we wont get the advantages of seamlessly loading, lazy-loaded relationships by simply traversing the associations.  So lets see the another interesting way of mapping the function that we just declared.

Creating the View:

Let's create a view that would make use of this newly defined function to return the desired information.  We can then map the view using NHibernate to return all the sub-accounts of a given manager at any given level.

The script to create the view would look like
The view definition is pretty simple we just make use of a Cartesian product between the Employees table.  We have made use of our function to find the sub-accounts who are under a given manager.

This view returns employee_id, employee_name, manager_id and manager_name.  This is very similar to what the function returns but, the fun part here is that, we have managed to get rid of the mandatory manager_id parameter.  We can simply execute the view without any manager_id and we will get the entire manager sub-account hierarchy.

If we execute the following query we would get all the managers and their sub-accounts
The results of the above query would look like
All Employees and their sub-accounts
Now that the view is defined lets map it using NHibernate.  We are going to use the same EmployeeSubaccount that we had used in the last post, but map it against the view this time.
As you can see the mapping is a lot richer now.  We can refer the Employee object from the EmployeeSubaccount class and make use of the many-to-one mapping just very easily.

The calling code to the view has also changed a bit.
That's all we need. As you can see this technique is a lot more cooler than the previous technique of mapping the CTE with a named query.

That's all folks for mapping CTE with NHibernate!

Thursday, November 15, 2012

Using NHibernate to fetch Hierarchical information with SqlServer - Part - 2

In my previous post, we saw how we could take advantage of Common Table Expression (CTE) to fetch Hierarchical information with SqlServer.

In this post we are going to see How we can make NHibernate use CTE to fetch full Hierarchical information for a given employee.

The Problem:

Just so that we are all on the same page, lets start by trying to understand what is the problem we are trying to solve.

One Manager can have many subordinates (employees working under him) and one employee can have many managers 
Because of this requirement we could build a complex multilevel hierarchy between managers and sub-accounts.  We want an ability to view all the sub-accounts of a given manager.  Sub-accounts would include direct sub-accounts or indirect sub-accounts.  As seen in the previous post we were able to get all the sub-accounts of Batman using the following CTE. 

If you look closely this CTE would return you all the subaccounts of Batman but what if we want all the subaccounts of some other manager? How do we integrate the CTE with NHibernate?

How do they do it?

Lets do it step by step.

Step One: lets remove the hard-coded manager_id from the CTE and get it into a variable.  The updated query looks like

Notice how we extracted the manager_id into a variable and we can now easily change the manager_id and get the result for any manager. But still, the question stands, How are we going to use it from NHibernate?

Step Two: To do so we need a function that takes in the manager_id as its argument.  We can then declare the CTE in the function and return the desired result for a given manager.  The function body would look like
Notice how we added the root_manager_id to be returned from the function. This will help us in the next step. Other than that we didnt do any major change to the CTE.

Step Three: We are going to look at two ways of integrating the CTE with NHibernate.  In this post we will look at a boring way of integrating the CTE with NHibernate.  In the next post we will look at an more interesting way of integrating the CTE with NHibernate.

Since we now have a function already defined, all we need to do is write a named query which will make use of this function to return the required information.
Named Query out of our way, lets look at the EmployeeSubaccount class and its Fluent NHibernate Mapping.
Nothing very alarming in the entity class and its mapping.

Notice that that we are using employee_id and manager_id as the composite primary key.  That's because one manager could have many sub-accounts and one sub-account could have many managers.

With the mapping in place lets look at the code to fetch all sub-accounts for any given manager.

We simply need a method that takes in the managerId and invokes the named query. This method returns the list of EmployeeSubaccount objects.

That's all folks! we have effectively made use of the CTE from NHiberante.

In the next post, we will look at how we can improve the mapping and get rid of the Named Query and still accomplish our task of fetching all sub-accounts of a given manager.

Sunday, October 7, 2012

Using NHibernate to fetch Hierarchical information with SqlServer - Part - 1

Recently in one of my project, we had a very interesting situation.  The relationship we were trying to model was pretty common and straight forward.  We wanted to map relationship between Employees and Managers.  The business rule was,

One Manager can have many subordinates (employees working under him) and one employee can have many managers  

I know! poor employee has to deal with more than one manager :)

As you might have guessed already, The mapping was pretty straight forward.  Employee table has a many-to-many relation with itself.  One manager could be a subordiante to someone else and one subordinate could be a manager for some other employees.

The domain class looked like

The fluent mapping looked like:

The many-to-many table that holds the employee-manager relationship is called employeemanager. The data in these tables looks like
Shows all the employees in the system
Many to many table which lists employees and their managers
Employees and their managers
As you can see.  The employee BatMan is right up in the ladder.  He has SpiderMan and HeMan as his subordinates.

HeMan and SpiderMan are the managers for SuperMan.  SuperMan is in-turn the manager of IronMan.

As you can see with only 5 employees we have a 3 level hierarchy build between our employees.

The query to get the direct subordinates of any given manager is simple enough and it looks like:
The output of this query would look like
Subordinates of BatMan
This query only returns me the direct subordinates of BatMan.  However, if you think about this for a moment, even SuperMan and IronMan are in someways subordinates of BatMan.  They are not the direct subordinates of BatMan but they are actually below BatMan.

The business requirement stated that, when a user logs in he should see all his subordinates - Direct or Indirect.

What kind of query would produce a result like that?

How do they do it?

What we really want is some kind of recursion so that we could go to any level to get all the subordinates of a given employee.  Different databases offer different ways in which such a query could be written. 

With SQL Server we could use a concept called Common Table Expression (CTE).  Using CTE we could build recursive queries.  The CTE query to get all subordinates of a given employee would look like

The above expression defines the CTE. I know it looks complex at the first go. But once you understand it its not that complex.

So lets break the query into parts and then decode it: What are the parts of the query:
This query looks very similar to the query to get all the direct subordinates of BatMan. Only difference is we have added a column called Level which will have value of 1 for all its rows. Notice that we are wrapping this query with the CTE called EmployeeHierarchy. After we have defined the CTE we can then make use of the CTE in subsequent query. Lets look at the next part of query
Next Part:
Now here is where the magic takes place. This query tries to get all the subordinates of managers selected from the CTE query. This query refers the EmployeeHierarchy defined in the previous query. In this query we are incrementing the level value by 1. This means this query will get me the Level 2 of subordinates of BatMan. Since the CTE is refered in its own defination we eventually get a recursive query.

If we try to run the following query:

All subordinates of BatMan are shown
As you can see we did get, SuperMan and IronMan listed as 2nd and 3rd level subordinates of BatMan!

Since there were only 5 users we could manage the data very well.

What would happen if we had some sort of cycle in our data.  For e.g. what would happen if data is setup in such a way that IronMan could also be a manager of HeMan?
If we run the CTE this time here's what you would get
Error caused due to Cyclic references in the DB
The problem is, we have a cycle in our data.  Because of this CTE is never able to end its loop.  And it throws an error after 100 recursive calls (You can change the value of number of recursion CTE should perform, by default its 100).  Increasing the number of recursion CTE should perform is not going to help us, it will still fail.

The only way to fix it is to not navigate the cyclic path over and over again.  For doing so, we introduce another field in our CTE called HierarchyPath.  The updated CTE looks like

The output of above query would look like
CTE with HierarchyPath.  Showing how we avoided cyclic recursion
The only change in this particular CTE is we have introduced a column called HierarchyPath.  This path will represent a breadcrumb of how an employee is related to a given manager.  To avoid navigating the cycle again and again, we added the where condition which checks whether the HerarchyPath build already has the subordinate listed before the given manager.

That's all for this post.  In the next post we will look at how to integrate the CTE with NHibernate.
Have some Fun!