The Location has a many-to-many relation with itself as below:

<class name="com.omnet.bean.Location" table="location">
<id name="locId" type="integer">
<column name="loc_id" sql-type="int" not-null="true"/>
<generator class="native"/>
</id>
<property name="locName" type="string">
<column name="loc_name" sql-type="varchar(50)" not-null="true"/>
</property>
........
<bag name="parent" lazy="true" table="location_relation">
<key column="child_id"/>
<many-to-many column="parent_id" class="com.omnet.bean.Location"/>
</bag>
</class>

the association table,location_relation, is as below:
CREATE TABLE device_relation (
child_id int(11) NOT NULL
parent_id int(11) NOT NULL,
) TYPE=InnoDB;

the following is sample data in the association table:
....
22-----------5
24-----------5
23-----------22
......

then how to find 5's child and grandchild ?

the right answer is 22,24,23.


regards !

...
Query q = sess.createQuery("select loc from com.omnet.bean.Location loc join loc.parent p where p.locId=:id");
q.setString("id",5);


it just returns 22,24.


I know it is a recursive question . Would you mind giving any suggestion ?


thanks in advance !

If you are only worried about direct children and grandchildren (i.e., not great-grandchildren), then you can use:

thanks for your reply !

the hierarchy level is un-determined !


regards!

This cannot be done in ANSI SQL.

Maybe i need to write a method in java to do the recursive job :(

i will try and hope there is another way to do it.


thanks !

The database you are using may be able to support this. Oracle, for example, has the CONNECT BY clause for hierarchical queries. If the database you use supports this, you may be able to do it using a native sql query in hibernate.

Aside from that, a nice way is to map both parent and children in your class. Then simply load the one you are interested in finding the chilren and grandchildren for and recursively loop over the mapped collection of chilren...

>>The database you are using may be able to support this. Oracle, for example, has the CONNECT BY clause for hierarchical queries. If the database you use supports this, you may be able to do it using a native sql query in hibernate.

It seems MySQL doesnot support hierarchical queries.


>>Aside from that, a nice way is to map both parent and children in your class. Then simply load the one you are interested in finding the chilren and grandchildren for and recursively loop over the mapped collection of chilren...

i feel that just map parent we can do the recursively loop too. would you mind show me an example ?



thanks in advance !

Then you can just recurse over the elements in the children List.


Also,

How? Having the collection of parents mapped would only allow you to navigate through ancestors, not descendants.

Up to now your solution is best .


thanks !

I have been coming up with the same issue using GORM/Grails to get a list of objects associated with nodes in an graph of categories and the best solution I came up with was a recursive loop through identical to the above, I don't think there is another way to do this?