My database is SQL Compact, which does not support CTEs. Although, as you can see from the outputted SQL in my previous post, it successfully uses sub-selects to optimally load the tree.
On Aug 3, 4:53 pm, Kim Johansson <[email protected]> wrote: > 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]> wrote: > > >> Ok but... Can you write an SQL to eagerly load recursive relation ? > >> 2009/8/3 HappyNomad <[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]> wrote: > > >>>> Can you write a SQL to eagerly load recursive relation ? > > >>>> 2009/8/3 HappyNomad <[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 -~----------~----~----~----~------~----~------~--~---
