Many-To-Many where attribute

dannyhoult · **Joined:** Sun Mar 30, 2008 5:19 pm **Posts:** 8

I have a Many-To-Many relationship between a User and a Project using a reference table called TeamMember. The TeamMember table also contains a column for TeamRole. Due to this instead of using a straight many-to-many I'm using a one-to-many to the TeamMember table that has a many-to-one to User.
This is all good and working correctly.
I want to restrict the collection to only Active users. If this was a straight many-to-many I could add the where="DELETED=0" on the many-to-many and be done with it (and have done this in other parts of the system). Seeing as I have to use the intermediary table and class because of the additional Role field in the reference table, how can I best implement the same where="DELETED=0" to get only Active Users?

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Business.Project, Business" table="PROJECT_CT" schema="INSIGHT">
    <id name="ProjectId" type="Decimal">
      <column name="PROJECT_ID"/>
      <generator class="sequence">
        <param name="sequence">PROJECT_SQ</param>
      </generator>
    </id>
    <property name="Name" type="String">
      <column name="NAME" length="100" not-null="true"/>
    </property>
    <!-- OTHER PROPERTIES HERE -->
    <bag name="ProjectTeamMembers" inverse="true">
      <key>
        <column name="PROJECT_ID" not-null="true"/>
      </key>
      <one-to-many class="Business.ProjectTeamMember, Business"/>
    </bag>
  </class>
</hibernate-mapping>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Business.User, Business" table="USER_CT" schema="INSIGHT">
    <id name="UserId" type="Decimal">
      <column name="USER_ID"/>
      <generator class="sequence">
        <param name="sequence">USER_SQ</param>
      </generator>
    </id>
    <property name="UserName" type="String">
      <column name="USER_NAME" length="256" not-null="true"/>
    </property>
    <property name="Deleted" type="Decimal">
      <column name="DELETED" not-null="true"/>
    </property>
    <!-- other properties here -->
    <bag name="ProjectTeamMembers" inverse="true">
      <key>
        <column name="TEAM_MEMBER_ID" not-null="true"/>
      </key>
      <one-to-many class="Business.ProjectTeamMember, Business"/>
    </bag>
  </class>
</hibernate-mapping>


<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Business.ProjectTeamMember, Business" table="PROJECT_TEAM_MEMBER" schema="INSIGHT">
    <id name="ProjectTeamMemberId" type="Decimal">
      <column name="PROJECT_TEAM_MEMBER_ID"/>
      <generator class="sequence">
        <param name="sequence">PROJECT_TEAM_MEMBER_SQ</param>
      </generator>
    </id>
    <many-to-one name="Project" class="Business.Project, Business" fetch="select">
      <column name="PROJECT_ID" not-null="true"/>
    </many-to-one>
    <many-to-one name="User" class="Business.User, Business" fetch="select">
      <column name="USRE_ID" not-null="true"/>
    </many-to-one>
    <many-to-one name="TeamMemberRole" class="Business.TeamMemberRole, Business" fetch="select">
      <column name="TEAM_MEMBER_ROLE_ID" not-null="true"/>
    </many-to-one>
  </class>
</hibernate-mapping>

dannyhoult · **Joined:** Sun Mar 30, 2008 5:19 pm **Posts:** 8

Perhaps the question wasn't posed properely. Any help on this would be fantastic and much appreciated - I'm tearing my hair out.

He's another example:
I have Student and Course Classes. with a many to many relationship.
Student Class:

Code:

  <class name="Student" table="student">
    <id name="StudentId" type="Decimal">
      <column name="STUDENT_ID"/>
    </id>
    <property name="Name" type="String">
      <column name="NAME" length="100" not-null="true"/>
    </property>
    <property name="Deleted" type="Decimal">
      <column name="DELETED" not-null="true"/>
    </property>
    <idbag name="Courses" inverse="true" table="StudentCourses">
      <collection-id column="STUDENT_COURSE_ID" type="Decimal">
      </collection-id>
      <key>
        <column name="STUDENT_ID" not-null="true"/>
      </key>
      <many-to-many class="Course" column="COURSE_ID"  />
    </idbag>
  </class>

Course Class:

Code:

  <class name="Course" table="course">
    <id name="CourseId" type="Decimal">
      <column name="COURSE_ID"/>
    </id>
    <property name="Name" type="String">
      <column name="NAME" length="100" not-null="true"/>
    </property>
    <property name="Deleted" type="Decimal">
      <column name="DELETED" not-null="true"/>
    </property>
    <idbag name="Students" inverse="false" table="StudentCourses">
      <collection-id column="STUDENT_COURSE_ID" type="Decimal">
      </collection-id>
      <key>
        <column name="COURSE_ID" not-null="true"/>
      </key>
      <many-to-many class="Student" column="STUDENT_ID"  />
    </idbag>
  </class>

This is all well and good. Now I want to only see non deleted courses or students when I traverse the from Student.Courses or Course.Students
I can do this simply by adding where="DELETED=0" to the many-to-many tags and viola!
in the Course Mapping:

Code:

    ...
   <many-to-many class="Student" column="STUDENT_ID" where="DELETED=0" />
   ...

and in the Student Mapping:

Code:

      ...
      <many-to-many class="Course" column="COURSE_ID"  where="DELETED=0" />
      ...

Now I want to throw another twist in the mix. Let's say I want to add another column to the StudentCourse table and seperate it into it's own class:

Code:

  <class name="StudentCourse" table="student_course">
    <id name="StudentCourseId" type="Decimal">
      <column name="STUDENT_COURSE_ID"/>
    </id>
    <property name="SomeProperty" type="String">
      <column name="SOMEPROPERTY" length="100" not-null="true"/>
    </property>
    <many-to-one name="Course" class="Course">
      <column name="COURSE_ID" />
    </many-to-one>
    <many-to-one name="Student" class="Student">
      <column name="STUDENT_ID" />
    </many-to-one>
  </class>

now that I have this defined I'll update the mappings of the Student to change the collection to a one-to-many to StudentCourse instead of a many-to-many to Course:

Code:

    ...
    <bag name="StudentCourses">
      <key>
        <column name="STUDENT_ID" not-null="true"/>
      </key>
      <one-to-many class="StudentCourse" />
    </bag>
   ...

and do the same from Courses to StudentCourse:

Code:

    ...
    <bag name="StudentCourses">
      <key>
        <column name="COURSE_ID" not-null="true"/>
      </key>
      <one-to-many class="StudentCourse" />
    </bag>
   ...

Now for the main question:

what can I do to only see students and courses that have a deleted property=0 (not deleted)
It was easy when it was a direct many-to-many by using the where="DELETED=0" attribute on the many-to-many tags.

What can I do when using this intermediary StudentCourse class as the go-between?

It seems like many people should have run into this scenario before. I was wondering what the solution is.
Any help would be GREATLY appreciated..

Thanks[/code]

wolli · **Posted:** Fri Jul 18, 2008 3:13 am

You could try filters. Have a look at

[url]http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/filters.html
[/url]

I'm not sure if the filter is used on a many-to-one (and if it makes sense), but you can give it a try. Remember that you have to enable the filter on the session manually.

Code:

<filter-def name="ActiveObjects"/>

<class name="Student" ....>
        <filter name="ActiveObjects" condition="(DELETED=0)"/>
</class>
<class name="Course" ....>
        <filter name="ActiveObjects" condition="(DELETED=0)"/> 
</class>

I think you also have to add not-found="ignore" to the many-to-one associations, since its possible, that the filter returns nothing (if it works at all).

dannyhoult · **Joined:** Sun Mar 30, 2008 5:19 pm **Posts:** 8

would using filters basically have the same functionality as using where="DELETED=0" on the class itself?

Code:

<class name="Student" where="DELETED=0">
    ...
</class>

although this get's me half way there, I would still like the ability to pull up deleted items on demand. In an interface to undelete etc. By using the above technique I can't load the deleted object anymore as nhibernate skips it.

Any thoughts?

dannyhoult · **Joined:** Sun Mar 30, 2008 5:19 pm **Posts:** 8

unfortunetely this did not work.
The StudentCourse collection contained objects that had null Courses (meaning the courses where deleted)

any other ideas?

wolli · **Posted:** Mon Jul 21, 2008 3:06 am

The filter condition is added to the where clause by hibernate. So there is no difference to the where attribute. But has some advantages over the attribute like you can enable and disable it for each session and you can use parameters for dynamic filtering.

Since you can enable and disable the filter on demand, it would be perfect for your scenario, where you want to allow an undelete. For normal use, you enable the filter and the user only gets the active courses. If you want to let him see everything, you load the data with a disabled filter.

Quote:

unfortunetely this did not work.
The StudentCourse collection contained objects that had null Courses (meaning the courses where deleted)

What do you mean with that ? Did you get an exception that there've been no object found for the many-to-one ? You can change that behavior with not-found="ignore".

dannyhoult · **Joined:** Sun Mar 30, 2008 5:19 pm **Posts:** 8

I already have have the not-found="ignore" attribute.
What I'm seeing is a collection of StudentCourse where the StudentCourse.Course is null.

I would rather the StudentCourse object not be returned in the list at all if it has a Course that's been deleted.