You asked "How can I execute a query which recursively retrieves the ENTIRE tree?" The ANSWER IS: use custom SQL.
Aren't you asking to write that SQL for you? 2009/8/4 HappyNomad <happynomad...@gmail.com> > > Thanks again for the effort, Dmitiry. I don't think this is relevant > to my situation, though, since I'm using SQL Compact Edition -- not > the standard edition SQL Server. > > One thing I have learned from this so far is that, apparently, an HQL > query is converted into exactly one SQL query. I'm not yet sure, > however, since this information arrived via an "invitation to think", > rather than any concrete advice. > > > On Aug 4, 6:38 am, Dmitiry Nagirnyak <dna...@gmail.com> wrote: > > 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/q... > > > > Cheers, > > Dmitriy. > > 2009/8/4 Cesar Sanz <the.email.tr...@gmail.com> > > > > > A SQL sentences that when executed in your RDBMS returns your records > > > recursively!! > > > > > ---- Original Message ----- > > > > > *From:* Kim Johansson <hagbarddenst...@gmail.com> > > > *To:* nhusers@googlegroups.com > > > *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 <fabioma...@gmail.com> < > fabioma...@gmail.com%3e> wrote: > > > > > Ok but... Can you write an SQL to eagerly load recursive relation ? > > > 2009/8/3 HappyNomad <happynomad...@gmail.com> <happynomad...@gmail.com > > > > > > > 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 <fabioma...@gmail.com> < > fabioma...@gmail.com> wrote: > > > > > Can you write a SQL to eagerly load recursive relation ? > > > > > 2009/8/3 HappyNomad <happynomad...@gmail.com> < > happynomad...@gmail.com> > > > > > 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 nhusers@googlegroups.com To unsubscribe from this group, send email to nhusers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
