Hibernate version: 3.1.3

Name and version of the database you are using: mySQL 5.0

Mapping documents:
<class name="Application" table="APPLICATIONS" polymorphism="implicit" >
...
<many-to-one name="memberClass" not-null="true" column="MEMBERCLASS_ID" class="MemberClass"/>
</class>

<class name="MemberClass" table="APPLICATIONMEMBERCLASS">
......
<set name="roles" lazy="true" cascade="save-update" table="APPLICATIONMEMBERCLASS_ROLES">
<key column="APPLICATIONMEMBERCLASS_ID"/>
<composite-element class="OrganizationRoleApplicationRole">
<many-to-one name="orgRole" class="OrganizationRole" "/>
<many-to-one name="applRole" class="ApplicationRole" "/>
</composite-element>
</set>

<set name="roledepts" lazy="true" cascade="save-update" table="APPLICATIONMEMBERCLASS_ROLEDEPT">
<key column="APPLICATIONMEMBERCLASS_ID"/>
<composite-element class="OrganizationRoleDepartmentApplicationRole">
<many-to-one name="orgRole" class="OrganizationRole" "/>
<many-to-one name="dept" class="Department" >
<many-to-one name="applRole" class="ApplicationRole" "/>
</composite-element>
</set>

<set name="users" lazy="true" cascade="save-update" table="APPLICATIONMEMBERCLASS_USERS">
<key column="APPLICATIONMEMBERCLASS_ID"/>
<composite-element class="UserApplicationRole">
<many-to-one name="user" class="User" />
<many-to-one name="applRole" class="ApplicationRole" />
</composite-element>
</set>
</class>



The following query always return null:

"select distinct oa from OrganizationApplication oa join oa.memberClass mc join mc.roles r join mc.users u join mc.roledepts d where oa.org.id=? and ((r.orgRole.id=?) or (d.dept.id=? and d.orgRole.id=?) or (u.user.id=?)) order by oa.id asc";

OrganizationRole, ApplicationRole, Department and User have their own seperate class mapping/tables.

Anything wrong in the above query ?

realized that the above query only works when the three Set in MemberClass are not null. if any of them is not set, then query does not work.

this is wierd:

For testing purpose, I changed the query string to:
"select distinct oa from OrganizationApplication oa join oa.memberClass mc where oa.org.id=? order by oa.id asc";

it can return the applications that org.id=?

however, once I add another join, it does not return anything:

"select distinct oa from OrganizationApplication oa join oa.memberClass mc join mc.roles r where oa.org.id=? order by oa.id asc";

any clue ?

the generated sql is :

select distinct
organizati0_.APPLICATION_ID as APPLICAT1_35_, ......
from APPLICATIONS organizati0_
inner join APPLICATIONMEMBERCLASS applicatio1_
on organizati0_.MEMBERCLASS_ID=applicatio1_.APPLICATIONMEMBERCLASS_ID
inner join APPLICATIONMEMBERCLASS_ROLES roles2_
on applicatio1_.APPLICATIONMEMBERCLASS_ID=roles2_.APPLICATIONMEMBERCLASS_ID
where organizati0_.DISCRIMININATOR='OrganizationApplication'
and ((organizati0_.ORG_ID=? ))
order by organizati0_.APPLICATION_ID asc

I think 'inner join' will return null if the linking table is empty. the or logic does not help even though one of the 3 linking tables is not null.