Hibernate query

Question

This is query regarding fetching documents. The document has two invitation type.

 ->OPEN : View all user
 ->LIMITED : Show to limited user

hibernate mapping is as below:

 <list name="invitedUsers" cascade="refresh" table="document_user_map">
 <key column="document_id"/>
 <index column="position" type="integer"/>
 <many-to-many class="com.v4common.shared.beans.usermanagement.User" column="user_id"/>
 </list>
 <property name="invitationType" column="invitation_type"  
 type="com.resources.userenum.UserEnumInvitationType" />

User can participate only those documents which are open and got invitation for limited type.

I can get expected result through below query:

select * from document left outer join document_user_map on document.id= document_user_map.document_id and document_user_map.user_id =648

But Through hibernate,I added below criteria in method but only getting limited tenders not others.

criteria.createAlias("invitedUsers", "invitedUser"); criteria.add(Restrictions.or(Restrictions.ne("invitationType",InvitationType.LIMITED), Restrictions.or( Restrictions.and(Restrictions.eq("invitationType",InvitationType.LIMITED),Restrictions.eq("invitedUser.id", user.getId())),Restrictions.isNull("invitedUsers"))));

can anybody help me where I am wrong.

Edit: user id 1 should get only document 1, 2 but not 3. but through this criteria I am not getting docid 1.

Table structure:document

id name invitation type userid
-----------------------------
1  doc1    open           1
2  doc2    LIMITED        1
3  doc3    LIMITED        2

Limited doc user entry added in this table:document_user_map

document_id user_id  position
---------------------------
2         1        0
3         2        0

Answer 1

The problem is solved through CriteriaSpecification.LEFT_JOIN. when Alias is created then it will do inner join, and it was not fetching data when join is null. so here I done left join in alias. and it will check for invited user.

criteria.createAlias("invitedUsers", "invitedUser",CriteriaSpecification.LEFT_JOIN); criteria.add(Restrictions.or(Restrictions.or(Restrictions.isNull("invitationType"), Restrictions.ne("invitationType",InvitationType.LIMITED)), Restrictions.and(Restrictions.eq("invitationType",InvitationType.LIMITED),Restrictions.eq("invitedUser.id", user.getId()))));