Parent-child tree hierarchy with NHibernate

Question

I have a Group. That group can contain groups and this needs to be mapped with NHibernate. I have done this before but this time I got an existing database that does not really store the data like I would like it too.

The database looks like this:

Groups

Id

Groups_Link

ParentId ChildId

I have no clue about how to map this?

Edit:

Seems like it's not that easy. One group can exist as a child to more than one group. So it should be an many-to-many association with itself I guess. Well, have yet to figure out how to do this. Any hints would be really appreciated!

Edit:

I thought views could help me solve the problem but there seems to be some restrictions on insert, update and delete with views that makes it difficult.

Answer 1

I mapped a similar structure like:

<class name="Folder" lazy="false">

    <id column="Id" name="Id">
        <generator class="guid"/>
    </id>

    <property name="Name" column="FolderText"></property>
    <bag  name="Children" cascade="all" fetch="subselect" inverse="true">
     <key  column="ParentId" />
     <one-to-many class="Folder" />
    </bag>

    <many-to-one class="Folder" name="Parent" column="ParentId" insert="true" />

Edit

I am taking a total shot in the dark now since your schema is not what I've used, and now I understand why you asked this:-) Anyways here's is a thought. Please let us know if this worked of if you find another solution:

 <join fetch="join" table="GroupLinks">
  <key column="ChildId"></key>
  <many-to-one name="Parent" class="Folder" column="ParentId"/>
 </join>

I might have the relationship backwards, but this at least would get you the parent.

Answer 2

As you say, it's just a many-to-many that happens to have the same class on both sides of the join. So:

<class name="G.Group, G" table="GROUPS">

  <id column="Id" name="Id">
    <generator class="guid"/>
  </id>

  <set name="Parents" table="Groups_Link" cascade="save-update">
    <key column="ChildId"/>
    <many-to-many class="G.Group, G" column="ParentId"/>
  </set>

  <set name="Children" table="Groups_Link" cascade="save-update" inverse="true">
    <key column="ParentId" />
    <many-to-many class="G.Group, G" column="ChildId"/>
  </set>

</class>

Answer 3

If you're using MS SQL (and maybe other RDBMS, not sure what others support it), you can use triggers on views to get around most of the common INSERT, UPDATE, DELETE restrictions.