Can I have self-referencing + one-to-many relationship ?

fly2moon2 · **Joined:** Mon May 22, 2006 12:12 am **Posts:** 23

Hi All,

I wanted to display Departments in a tree control. A department (or section) can be a sub-division of another department. So, I have it self-referencing (column named : many-to-one parentDept). It is fine so far to display on the tree control. However, with this arrangement, I've to always build the tree bottom-up, i.e. from the lowest (chind) deparement -> look for its parentDept -> to the highest (parent) department. The tree is inverse and not logical in common sense.

I wanted to have the parent department first then the next level until the lowest. So, I tried using a set (named depts) to have set of one-to-many. But my result fails.

My strategy could be wrong. Need your help to tell me any common method to do this appropriately? Thanks!

The Department hbm.xml:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="RHU.RAIS.Business.Department, RHU.RAIS.Business" table="Department">
<id name="deptId">
<column name="deptId" not-null="true"/>
<generator class="native" />
</id>
<property name="deptLevel">
<column name="deptLevel" />
</property>
<property name="deptName">
<column name="deptName" length="255"/>
</property>
<many-to-one name="parentDept" class="RHU.RAIS.Business.Department, RHU.RAIS.Business" cascade="none" insert="true" update ="true" outer-join="auto" column="parentDept"></many-to-one>
<many-to-one name="parentOrg" class="RHU.RAIS.Business.Organization, RHU.RAIS.Business" cascade="none" insert="true" update ="true" outer-join="auto"></many-to-one>
<set name="depts"
inverse="true"
cascade="all-delete-orphan">
<key column="parentDept" />
<one-to-many class="RHU.RAIS.Business.Department, RHU.RAIS.Business"></one-to-many>
</set>
</class>
</hibernate-mapping>

my recursive function HOPING to load the children department recursively:

private void addNodePropRecursiveOrgDept(ref TreeNode node, Department dept)
{

if (dept.depts.Count > 0)
{
for (IEnumerator ie = dept.depts.GetEnumerator(); ie.MoveNext(); )
{
Department deptChild = (Department)ie.Current;
node = node.Nodes.Add(deptChild.deptId.ToString(), deptChild.deptName, (int)Constants.ImageListIndex.DepartmentObject, (int)Constants.ImageListIndex.DepartmentObject);
if (!dept.Equals(deptChild))
{
addNodePropRecursiveOrgDept(ref node, deptChild);
}
}
}
}

rolandz · **Posted:** Sun Jun 25, 2006 2:34 am

I have a simple picture database where I have albums that can have many subalbums. I have a following mapping for class Album

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="DB.Data.Album, DB.Data" table="ALBUM">
      <id name="Id" column="ID" unsaved-value="-1">
         <generator class="sequence">
            <param name="sequence">ALBUM_ID_GEN</param>
         </generator>
      </id>
      <property name="Name" column="ALBUM_NAME" />
      <property name="Description" column="ALBUM_DESCRIPTION" />
      <many-to-one name="Parent" column="PARENT_ID" class="DB.Data.Album, DB.Data" />
   </class>
</hibernate-mapping>

Following is the Album class

Code:

using System;

namespace DB.Data
{
   /// <summary>
   /// Description of Album.
   /// </summary>
   public class Album
   {
      Album()
      {
      }
      
      public Album(string name)
      {
         this.name = name;
      }
      
      public Album(string name, string description)
      {
         this.name = name;
         this.description = description;
      }
      
      int id = -1;
      
      public virtual int Id {
         get {
            return id;
         }
         set {
            id = value;
         }
      }
      
      Album parent;
      
      public Album Parent {
         get {
            return parent;
         }
         set {
            parent = value;
         }
      }
      
      string name;
      
      public virtual string Name {
         get {
            return name;
         }
         set {
            name = value;
         }
      }
      
      string description;
      
      public virtual string Description {
         get {
            return description;
         }
         set {
            description = value;
         }
      }
      
      public override string ToString()
      {
         return Name;
      }
      
   }
}

I also have a data manager which can provide me a collection of objects as follows:

Code:

public T GetBy(string prop, object val)
{
   using(NHibernate.ISession sess = factory.OpenSession())
   {
      NHibernate.Expression.ICriterion crit;
      if(val != null)
      {
         crit = NHibernate.Expression.Expression.Eq(prop, val);
      }
      else
      {
         crit = NHibernate.Expression.Expression.IsNull(prop);
      }
      return (T)sess.CreateCriteria(typeof(T))
         .Add(crit)
         .UniqueResult();
   }
}

Then I ask data manager for all albums having "Album" property set to null. Then I am recursing throug every item and so on. This way I have a full tree of albums.

I know that this is not optimal and could be improved but it works perfectly. Hope this helps you :)

donkanmcleod · **Posted:** Wed Nov 19, 2008 10:15 am

hi guys, i have a similar problem with an entity with self-joined children.
So, i'm using Nhibernate, for .net, and sql server with identity columns.
I tried to set the "<set>" tag for reference the cihldren in select and insert/update operations, but, the ID property was a problem for nhibernate.
finally i decided to use two sets, uno for select operations with id property included and set for insert/update operations. Is not an elegant solution, but it works for me.
If you know any way more elegant please post it!

this is my hbm.xml code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="ApplicationCore.Domain.Menu" assembly="ApplicationCore">

<class name="ApplicationCore.Domain.Menu.MenuItem" table="APPLICATION_MENU">

<id name="ID">
<column name="ID" />
<generator class="identity" />
</id>

<property name="Name">
<column name="NAME" />
</property>

<property name="ItemType">
<column name="ITEM_TYPE" />
</property>

<set name="SubItemsSave" table="APPLICATION_MENU" >
<key column="PARENT_ID" />
<composite-element class="MenuItem">
<property name="Name" column="NAME"/>
<property name="ItemType" column="ITEM_TYPE" />
</composite-element>
</set>

<set name="SubItemsRead" table="APPLICATION_MENU" >
<key column="PARENT_ID" />
<composite-element class="MenuItem">
<property name="ID" column="ID"/>
<property name="Name" column="NAME"/>
<property name="ItemType" column="ITEM_TYPE" />
</composite-element>
</set>

</class>

</hibernate-mapping>

and my .net C# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Iesi.Collections.Generic;

namespace ApplicationCore.Domain.Menu
{

public class MenuItem : RegistroMantenedor
{
public virtual MenyItemType ItemType { get; set; }

public virtual ISet<MenuItem> SubItemsSave { get; set; }
public virtual ISet<MenuItem> SubItemsRead { get; set; }

public static Type Type()
{
return new MenuItem().GetType();
}
}

public enum MenyItemType
{
TypeRoot,
TypeSeparator,
TypeCommand,
TypeMainOption
}

}