THere are 3 tables:

UserRole (id, name)
LoginUserRole(login_id, user_role_id)
UserRolePerm(id, user_role_id, project_id, ...)

We need to get a list of user roles for a given user, project. The SQL would be...

SELECT login_user_role.user_role_id
FROM login_user_role
JOIN user_role_permissions
ON login_user_role.user_role_id = user_role_permissions.user_role_id
WHERE login_id = 1
AND user_role_permissions.project_id = 1
ORDER BY login_user_role.user_role_id


How can this be done with Hibernate Criteria API?

It seems like there's no way to do this query since LoginUserRole and UserRolePermissions are not directly related. (They're related by user_role_id which is what the JOIN is occurring on with SQL query)

Thx,
tshah