Yes, my original wording of "execute a query" was off. In in my post yesterday, I clarified my question:
"I am not asking about how to load the entire tree in one round trip [SQL query]. I've already investigated that issue, and it is impossible for my database (CTEs are not supported by SQL Compact). What I'm asking is: what code can I write against the NH [ISession] API that will [efficiently] retrieve the entire Category tree AS IF I had lazy="false" on the SubCategories mapping." I've mentioned a few times on this thread that CTEs are not supported by SQL Compact, and I believe this precludes the sort of SQL solution you have in mind. So I don't think writing SQL is the right approach. A better approach may be to find out how to invoke, on a case by case bases, the same functionality that lazy="false" on the list mapping invokes. I never thought about it before, but it seems there's some fundamental distinction between what NH functionality can be invoked using mapping attributes, and what can be invoked using ISession's API. On Aug 4, 9:07 am, Dmitiry Nagirnyak <[email protected]> wrote: > 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 <[email protected]> > > > > > 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 <[email protected]> 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 <[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 -~----------~----~----~----~------~----~------~--~---
