I have just talked to my DBA and it seems the whole hierarchy CAN be loaded with a single SQL statement.
In Oracle there's CONNECT BY. Sql Server is not as good here but still can do the job: http://dbbest.net/blog/?p=44 Probably other DBs can do it too. I don't *think* NH supports such stuff, So if there's really such a need, then it is better to execute custom SQL that returns the whole hierarchy. Here is some info on custom SQL: https://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/querysql.html Cheers, Dmitriy. 2009/8/4 Cesar Sanz <[email protected]> > A SQL sentences that when executed in your RDBMS returns your records > recursively!! > > ---- Original Message ----- > > *From:* Kim Johansson <[email protected]> > *To:* [email protected] > *Sent:* Monday, August 03, 2009 3:53 AM > *Subject:* [nhusers] Re: Eagerly load recursive relation > > Kids speak: Can you write a SQL query and execute that in your RDBM's tools > that loads something recursively? > > HappyNomad wrote: > > Really sorry, but I'm still drawing a blank. If you provide some > sample code then it would be easier for me to understand your > suggestion. > > > On Aug 3, 4:41 pm, Fabio Maulo <[email protected]> > <[email protected]%3e> wrote: > > > Ok but... Can you write an SQL to eagerly load recursive relation ? > 2009/8/3 HappyNomad <[email protected]> <[email protected]> > > > > > > Hi Fabio, thanks for the reply. But I'm not sure I understand the > nature of your question. In any case, the query would need to be in > HQL, and not SQL, since I need to load the actual objects, and not > just scalar values. > > > On Aug 3, 4:32 pm, Fabio Maulo <[email protected]> <[email protected]> > wrote: > > > Can you write a SQL to eagerly load recursive relation ? > > > 2009/8/3 HappyNomad <[email protected]> <[email protected]> > > I have a recursive one-to-many relationship that has the default lazy > value of true. How can I execute a query which recursively retrieves > the ENTIRE tree? > > > Here's the recursive one-to-many relationship: > > > <class name="Category" lazy="false"> > ... > <list name="SubCategories" fetch="subselect"> > <key column="ParentCategoryID"/> > <index column="PositionInList"/> > <one-to-many class="Category"/> > </list> > > > I don't specify lazy="false" on the list since laziness is required in > about half the queries I need to run. I have fetch="subselect" on the > list as an optimization for when I do manage to retrieve the entire > tree. > > > I've tried the ICriteria API: > session.CreateCriteria<Category>().SetFetchMode( "SubCategories", > FetchMode.Eager ).Add( Restrictions.IsNull > ("ParentCategory") ).SetResultTransformer > ( CriteriaSpecification.DistinctRootEntity ).List<Category>(); > > > but that only eagerly loaded only the first level in the hierarchy. > > > -- > Fabio Maulo > > > -- > Fabio Maulo > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
