-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
n1ck0s
 Post subject: How to do a recursive query?
PostPosted: Wed Jun 07, 2006 4:48 am 
Newbie

Joined: Wed Jun 07, 2006 3:15 am
Posts: 14
I am sorry if this is asked before, but I couldn't find anything relevant.

I have a tree structure of Categories (using fk parentID). Each leaf category contains Products.

All I need is a HQL query (or whatever else) that given a certain Category, it will fetch all Products which belongs in this Category or any of its children.

The only "solution" I found so far (without changing the tables) is to do multiple self joins, one for each level of the tree, like this

inner join Categories as Categories
inner join Categories as SubCategories
inner join Categories as SubSubCategories

, but there must be a better way...

I hope I make sense...
Top
 Profile  
 
antoska
 Post subject:
PostPosted: Wed Jun 07, 2006 9:45 am 
Beginner
Beginner

Joined: Wed May 03, 2006 5:10 am
Posts: 32
Location: Monopoli - Italy
Hi,

in my test project I realized your structure in xml with lazy loading:
Master --> MasterDetails --> MasterDetailDetails

Code:
  <class name="ObjectModel.Test.Master, ObjectModel" table="Test_Master">
    <id name="Id" type="Int32" column="Id" access="field.pascalcase-m-underscore">
      <generator class="hilo">
        <param name="table">NH_Key_Test</param>
        <param name="column">Test_Master</param>
        <param name="max_lo">0</param>
      </generator>
    </id>
    <version name="RowVersion" column="RowVersion" type="Int32" unsaved-value="negative" />
    <property name="Description" column="Description" type="String" />
    <bag name="masterdetails" inverse="true" cascade="all" lazy="true">
      <key column="IdMaster" />
      <one-to-many class="ObjectModel.Test.MasterDetail, ObjectModel" />
    </bag>
  </class>

  <class name="ObjectModel.Test.MasterDetail, ObjectModel" table="Test_MasterDetail">
    <id name="Id" type="Int32" column="Id" access="field.pascalcase-m-underscore">
      <generator class="hilo">
        <param name="table">NH_Key_Test</param>
        <param name="column">Test_MasterDetail</param>
        <param name="max_lo">0</param>
      </generator>
    </id>
    <version name="RowVersion" column="RowVersion" type="Int32" unsaved-value="negative" />
    <property name="Description" column="Description" type="String" />
    <many-to-one name="Master" column="IdMaster" class="ObjectModel.Test.Master, ObjectModel" cascade="none" />
    <bag name="masterdetaildetails" inverse="true" cascade="all" lazy="true">
      <key column="IdMasterDetail" />
      <one-to-many class="ObjectModel.Test.MasterDetailDetail, ObjectModel" />
    </bag>
  </class>

  <class name="ObjectModel.Test.MasterDetailDetail, ObjectModel" table="Test_MasterDetailDetail">
    <id name="Id" type="Int32" column="Id" access="field.pascalcase-m-underscore">
      <generator class="hilo">
        <param name="table">NH_Key_Test</param>
        <param name="column">Test_MasterDetailDetail</param>
        <param name="max_lo">0</param>
      </generator>
    </id>
    <version name="RowVersion" column="RowVersion" type="Int32" unsaved-value="negative" />
    <property name="Description" column="Description" type="String" />
    <many-to-one name="MasterDetail" column="IdMasterDetail" class="ObjectModel.Test.MasterDetail, ObjectModel" cascade="none" />
  </class>



You have to create 3 classes reflecting this xml structure and load the collections.

Could you be interested in this kind of solution?

Antonella
Top
 Profile  
 
n1ck0s
 Post subject:
PostPosted: Wed Jun 07, 2006 10:45 am 
Newbie

Joined: Wed Jun 07, 2006 3:15 am
Posts: 14
If I understand correctly, its the same logic as to do multiple joins on the same table.

I could always of course build a recursive function that would use the ChildCategories (lazy loading) collections and return a collection with everything it finds. Then from that collection I could build another one accumulating all the ChildProducts.

But I think all this is ungly... I am trying to find an elegant solution that will work with trees of any depth and without recursion through code. I am not sure it can be done, but I am new to nHibernate and I may miss something.

The only solution I found required changing the way the hierarchy is stored in the db (nested set model instead of the adjustency). I think "Hibernate in Action Second Editon" has an example of an implementation of the nested set model.
Top
 Profile  
 
jemiller
 Post subject:
PostPosted: Wed Jun 07, 2006 4:25 pm 
Pro
Pro

Joined: Fri Nov 19, 2004 5:52 pm
Posts: 232
Location: Chicago, IL
I've been wondering the same thing. My assumption so far has been that it can't be done other than the way that you described. My guess is that it's just a limitation of SQL?

If you do find a way to do it, please post to the forum because I'd definitely be interested in learning how.

As for me, I've been doing it the "ugly" way as that's the only way I know how. It's certainly not very efficient.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.