Is there a way to do a many to many for two tables?

Lets say I have a School table, SchoolToPeople link table, Professor table, Student Table.

The link could be:
School -> SchoolToPeople (schoolId, personId) -> Professor (professorId)
or
School -> SchoolToPeople (schoolId, personId) -> Student (studentId)

I've tried...

<bag name="Professors" table="SchoolToPeople" lazy="true" >
<key column="schoolId"></key>
<many-to-many class="Professor" column="personId" />
</bag>

<bag name="Students" table="SchoolToPeople" lazy="true" >
<key column="schoolId"></key>
<many-to-many class="Student" column="personId" />
</bag>


This results in getting an error saying "No row with the given identifier exists"

There doesn't seem to be a way to tell the many to many mapping that personId is in SchoolToPeople and studentId is for Student.


Help on this would be greatly appreciated!


Thanks,
Stephen

P.S. No I'm not a student in college or something. I'm just using this as an example.

The mapping looks ok. Is it possible that there are no Professors or Students for a Scholl ? Then you have to add not-found="ignore" to the many-to-many definition.

No, the problem is that since SchoolToPeople uses personId and Professor uses professorId. Since I've defined it to use personId, it seems like its trying to find personId in the Professor table, which doesn't exist.

And what's the relation between personId and professorId ?

The relation can go two directions from School

School(schoolId) -> SchoolToPeople(schoolId, personId) -> Professor(professorId)

or

School(schoolId) -> SchoolToPeople(schoolId, personId) -> Student(studentId)


I think I found the problem, but I haven't been able to find a solution for this just yet. What seems to be happening is it's doing a left outer join to professor from SchoolToProfessor, thus returning two records. one of them being null, since the null one points to a student, not a professor.

If nhibernate did this join using an inner join, then the null personId wouldn't be returned.

I hope this clarifies it some.


Thanks,
Stephen

You probably need to separate join tables SchoolToStudent and SchoolToProfessor. Or you use one bag for students and professors and define separate access properties which will do the separation.

I was afraid I might have to separate out the table into two. It's unfortunate you can't use inner join instead of left outer join. If this was possible, this would deffinately solve it.

I'll look into the bag access idea. Would you mind providing me an example of this type of config?

Thanks,
Stephen

The "shared" bag will only work if Students and Professors are stored in the same table. If that's the case, you can map one bag with People instead of two separate bags for Students and Professors.

Then you define properties on your class:

IList<Professor> Professors
{
get { return the Professors from the People List }
}

and the same for Students. Adding and removing objects from these lists will be the tricky part. So I recommend using two tables.

Thanks. As I said before, if we could force a many to many to do an inner join, then this would solve this problem.

You wouldn't happen to know why they choose left outer join instead of inner join?


Thanks again,
Stephen

You can generally disable outer join fetching, but that's a global setting and not for a special class.