[b]Hibernate version:[/b]
1.2.0.4

[b]Mapping documents:[/b]
<?xml version="1.0" encoding="utf-16"?>
<hibernate-mapping auto-import="true" default-lazy="false" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:nhibernate-mapping-2.2">
<class name="GamePlan.Models.User, GamePlan" table="User">
<id name="Id" access="property" column="Id" type="Int32" unsaved-value="0">
<generator class="native">
</generator>
</id>
<set name="Projects" access="property" table="ProjectUser" lazy="false" inverse="true" where="project.IsComplete is null">
<key column="UserId" />
<many-to-many class="GamePlan.Models.Project, GamePlan" column="ProjectId"/>
</set>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-16"?>
<hibernate-mapping auto-import="true" default-lazy="false" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:nhibernate-mapping-2.2">
<class name="GamePlan.Models.Project, GamePlan" table="Project">
<id name="Id" access="property" column="Id" type="Int32" unsaved-value="0">
<generator class="native">
</generator>
</id>
<property name="CreatedOn" access="property" type="System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<column name="CreatedOn"/>
</property>
<set name="Users" access="property" table="ProjectUser" lazy="false">
<key column="ProjectId" />
<many-to-many class="GamePlan.Models.User, GamePlan" column="UserId"/>
</set>
</class>
</hibernate-mapping>

[b]Name and version of the database you are using:[/b]
SQLite 3.3.17

My goal is to have the Projects set on the User class only retrieve those projects with a null CompletedOn. I wasn't able to find any posts related to putting a where clause on the far side of a many-to-many relationship. Anyone know if this is possible? If so, can you point me in the right direction?

Hey Bill,
The where clause is supposed to be an arbitrary raw sql clause. So your mapping in User could be:


I think it writes your where clause verbatim to the sql it gens.

The mapping you provided was using IsComplete is null, but I think that was just a goof . (?)

MIKE :)

Hey Mike. I didn't know you trolled the forums helping lost souls :)

I had tried your solution but the problem is that it applies the arbitrary where clause to the join table, ProjectUser, instead of the Project table. So I end of with sql similar to the following:

SELECT projects0_.UserId as UserId__1_, projects0_.ProjectId as ProjectId1_, project1_.Id as Id5_0_, project1_.CompletedOn as Complete5_5_0_ FROM ProjectUser projects0_ left outer join Project project1_ on projects0_.ProjectId=project1_.Id WHERE projects0_.CompletedOn is null and projects0_.UserId=?

Any thoughts?

I try to get on once a day to answer a question here...my way of contributing I guess.
Apparently I didn't do much good here tho.
What happens if you put the where clause directly on the many-o-many element?



MIKE