Hello,
I'm playing around with NHibernate, trying to map some of my tables. I have a many to many relationship which uses 2 tables A and B, A is connected to A (again) via B. B also contains a column to specify the kind of relationship.

So, briefly:
A: ID, SomeThing
B: FromID, ToID, TypeOfRelationship

I already added a bag to my mapping file to query for "children" objects. Now I want to add a filter, to pull up only children having TypeOfRelationship = SomeValue.
I suppose I have to use where in XML, but when I put where="TypeOfRelationship=1" the resultant SQL shows TableA_Alias.TypeOfRelationship = 1. It have to look for TypeOfRelationship field in table B, not A. Also, forcing B.TypeOfRelationship = 1 in where clause, outputs literealy the same where in SQL, but due to the fact that A and B are both aliased in TableA_Alias and TableB_Alias, B is an unknown table in query.

In the sample below you can see: "where item1_.RelationshipType=1" but I need to produce "where costs0_ .RelationshipType=1"

How can I solve this?

TIA, mt


Hibernate version:
1.2.1

Mapping documents:


The generated SQL (show_sql=true):


Name and version of the database you are using:
MS SQL 2005

You can't have additional columns in a many-to-many table (at least hibernate can't use them). You can define a class Relationship which contains both ids and the relationship type.