Hi,

I want to fetch data in hierarchical order from database using HQL.

I have a table with self reference

Item
------------
Item_Id
Item_Name
Parent_Item_Id

Class Item
{
int Id;
string Name;
List<Item> Children;
}

Now I want to fetch all the data till the last child.
If I run the query

from Item item join fetch item.Children

Then it will fetch all the children of first level items not the whole hierarchy. I want to fetch whole hierarchy in single hql query.

Can anyone please help?

Thanks in advance :)

Regards,
Niranjan

Hi Niranjan,

This would be translated into a 'connect by' query to the underlying database. Unfortunately, Hibernate doesn't support this as of yet.

Only options are to work with views (abstract the recursive connection) but this may not always work as you'd need to know how 'deep' the recursion goes beforehand or to programmatically iterate through and dive into the result sets. I.e. select individual records and use it's content to fire a new query (to select it's child of parent). This will not be very efficient, tough.

Regards,

Arjan

Do you need to be able to read selected "subtrees" (e.g. one item and all of its subitems) ? In that case you probably need a SQL Query. If you only need to read the complete tree, it's easier because you can simply load the complete table and hibernate creates the hierarchy for you (at least its working in my case).

Hi Wolli,

In my project, to support multiple databases I have used NHibernate. As you have suggested to use Sql query then the purpose is gone. So I need something from Nhibernate such as "Connect by" clause in oracle and "With statement" in sql server.

That will be more beneficial for me.

Thanks,
Niranjan

NHibernate doesn't support that. Afaik you can write custom functions and there corresponding sql for each dialect, but I've never done that and can't give you much information about that.

Something like that:

http://stackoverflow.com/questions/1845884/custom-sql-function-for-nhibernate-dialect