Hello there. I have an scenario where I have to load (efficiently, with one query) a bunch of objects that contain references to object of their same types, I mean, a tree of objects.

Example:

public Class ClassA {
public int Id;
public string Name;
public ClassA Parent;
public Set<ClassA> MyChildren;
public Set<ClassX> MyList2;
public Set<ClassY> MyList3;
}

I would like to write an HQL for this class to retrieve all the tree of ClassA objects. The first ones should be the ones that have a null Parent (the ones which belong to the root Parent), and the method should specify to retrieve all existent child nodes, but not load the MyList2 neither MyList3 properties (I mean, they should remain -lazy-).

How could I do this? Is there an example of this type of implementation in any tutorial?

Thanks in advance.

if the collections are marked as lazy they wont be loaded. The problem is that you can't do recursive queries with HQL. If you're using Oracle or SQL Server 2005 their SQL implementation support recursive queries but you have to use native SQL queries from your session object.

cheers,
Radu

Thanks for your reply radu!

However, it has been somewhat frustrating...

Isn't there a way to do this efficiently without using SQL? I think that before using NHibernate, in this project this transaction was done by a stored procedure that created a temporal table for the purpose.

Isn't there a cross-db way to make recursive queries? :( Is it planned in the roadmap the feature of recursion in HQL?

Regards.

Andrés [ knocte ]

--

The problem lies int the SQL itself. As far as I know, recursive SQL queries are non standard so vendors implement proprietary extensions. This means that currently is not possible to have a cross-db recursive query implementation in SQL. As HQL translates to SQL this means that you wont have recursive HQL queries either.

If anyone knows better I would appreciate a reply as I'm interested myself in an elegant solution to this problem!

My current solution (under SQL Server 2000) is to store the path and the level of the row in the tree so I'm able to retrieve whole branches (or the whole tree) with only one SQL query. When inserting or updating rows I have a trigger which updates the path info. The solution is not elegant but works. Anyway for big trees which are displayed in the UI, I'm not retrieving whole branches or the whole tree, I preffer to do on-demand population of the treeview which requires only the child collection of an item.

cheers,
Radu

This could be implemented by Hibernate in a recursive java code that would perform many selects, couldn't it? Is it already done?

I have the same situation. I don't want to put "lazy=false" in the mapping because I don't want to load all children all the time, but in a specific point I need to write a query or Criteria that could load the entire tree.
Anyone knows how should I proceed?

If Hibernate could do it for me I think it would be more elegant than having to visit the entire tree just to activate the proxies - the tree will be used out of the session scope.

Thanks!

If you are using SQL Server, you can do that with a SQLQuery like this:



In this example, I only retrieve the ids, but entities should work as well. I'm not sure why I replaced the parameter instead of using .SetParameter(), maybe that didn't work here.

Hi knocte,

This is a common problem in a relational database. Various ways of implementing this have been documented:
http://www.dbazine.com/oracle/or-articles/tropashko4

'Materialized Path' is one I've used before. You can add an extra property to your class, and query using the 'like' operator to get sections of a tree.

Note, these patterns are neither database-specific, nor ORM specific.

Regards,
Richard