Hi there,

I have searched the forums for self-referencing postings but could not find any.

I have created a table that has a foreign key to itself to create a parent-child relationship (1 parent can have many children). I would like to know what hibernate query to run to get a list of all rows with their self-reference attributes populated.

Thanks in advance :)

Here is the mapping file (created by middlegen):
-----------
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC

"-//Hibernate/Hibernate Mapping DTD 2.0//EN"

"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >



<hibernate-mapping>

<!--

Created by the Middlegen Hibernate plugin



http://boss.bekk.no/boss/middlegen/

http://hibernate.sourceforge.net/

-->



<class

name="com.crisys.sf.dto.PerspectiveElement"

table="PerspectiveElement"

>



<id

name="id"

type="java.lang.String"

column="id"

>

<generator class="assigned" />

</id>



<property

name="perspectiveName"

type="java.lang.String"

column="PerspectiveName"

not-null="true"

length="50"

/>

<property

name="className"

type="java.lang.String"

column="ClassName"

not-null="true"

length="100"

/>

<property

name="context"

type="java.lang.String"

column="Context"

length="100"

/>

<property

name="position"

type="java.lang.String"

column="Position"

length="50"

/>



<!-- associations -->

<!-- bi-directional one-to-many association to PerspectiveElement -->

<set

name="perspectiveElements"

lazy="true"

inverse="true"

>

<key>

<column name="Parent" />

</key>

<one-to-many

class="com.crisys.sf.dto.PerspectiveElement"

/>

</set>

<!-- bi-directional many-to-one association to PerspectiveElement -->

<many-to-one

name="perspectiveElement"

class="com.crisys.sf.dto.PerspectiveElement"

not-null="true"

>

<column name="Parent" />

</many-to-one>



</class>

</hibernate-mapping>

A good way to determine a HSQL query is to write down the query in SQL
first. Then replace tablenames by classnames, fields by attributes.
It would be helpful, if you could post your SQL-Statement.

Jens

Thanks Jens,

Ive got a query in HQL now that is close to correct. This returns x number of objects which each have 0 to n children in their set.

What I would like the query to do is go through the children and do the join for their children recursively (untill it reaches a row that has no children). The result would be a set of graphs.

Here is my query so far (each PerspectiveElement contains a set of children, called perspectiveElements).
----------------------------------

"from PerspectiveElement as pe left join fetch pe.perspectiveElements"

Need to make lazy = false for the set of children as follows

<set
name="perspectiveElements"
lazy="false"
inverse="true"
>