Saturday, 20 March 2010

Nhibernate many-to-many with extra fields in the join table

This blog post has been inspired by a twitter exchange I noticed at some point this week.  @jagregory tweeted that one of the most common questions on the FluentNhibernate forum was how to model a many-to-many relationship where the join table has extra fields.  

I want to blog about the approach that I and I am sure countless others use.

You will have to forgive me for the archaic xml mapping files that I am going to use in this example.  I have not made the jump to brave new world of the fluent interface of FluentNibernate. 

In a typical many-to-many mapping, like the one below, the join table does not exist in the object world:

<bag name="DistributionList" table="DistributionList" lazy="true" cascade="save-update" inverse="true">
    <cache usage="read-write"/>
    <key column="planuid"/>
    <many-to-many class="ncontinuity2.core.domain.Contact,ncontinuity2.core" column="contactuid" not-found="exception"/>

But what if we have extra fields in the join table?  

If for example, we have the following tables in our database schema:


You can see that the UserPermissions table contains extra fields apart from the two foreign keys of the linking tables.  In the above example the UserPermissions  join table has the extra fields of viewallowed and editallowed

Here is the mapping I use:

<map name="UserPermissions" table="UserPermissions" lazy="true" cascade="none">
    <cache usage="read-write"/>
    <key column="useruid" />
    <index column="name" type="String" length="100"/>
    <composite-element class="ncontinuity2.core.domain.UserPermission,ncontinuity2.core">
        <parent name="User"/>
        <property name="ViewAllowed" column="viewallowed" type="Boolean" />
        <property name="EditAllowed" column="editallowed" type="Boolean" />
        <many-to-one name="Permission"  class="ncontinuity2.core.domain.Permission,ncontinuity2.core" column="permissionid" cascade="all"/>

The above mapping is from my user.hbm.xml file that models the user as having a dictionary of composite elements which map to a class named UserPermission.  

Here is how this looks in a class diagram:

The following should be noted from the above diagram:

  1. The User object contains a dictionary of UserPermission ojects.
  2. The UserPermission object contains both the Permission object, which is the other side of the man-to-many relationship and the extra fields of the join table.

Hopefully this gives you another option to achieve the desired result.

I am not saying I am right, I am just saying how I do it.

1 comment: