Hibernate version:
2.1.8
Name and version of the database you are using:
PostgreSQL 7.4.x
I have three object: Role, Group, and User, linked via many-to-many relationships
Code:
Role <----> Group <-------> User
m..n m..n
(role_group) (group_user)
I am trying to do an HQL query to get roles for a user via groups, like
Code:
from Role r
join r.groups as theGroup
join theGroup.users as theUser
where theUser.username = :user
This
almost works, except for returning the same Role multiple times for each user if the user is in the role by virtue of membership in different groups.
If I were coding this in straight SQL, I would probably do something like
Code:
... from role where
exists (select 1 from group_role gr
join group g using(group_id)
join user u using(user_id)
where u.username=:user)
But I can't think of any way that I can reference 'group_role' in HQL, because it is a collection support table (created in @hibernate.set table="...") rather than an entity. The closest I've come is something like
Code:
from Role
where exists (from Group g join g.users as theUser where g in elements(r.groups))
but I've found that the 'in elements(...)' results in a nested subquery that is not as scalable as going directly against the junction table would be.
Is there a way to do this in Hibernate, or is this one of those times when you should use a direct SQL query? Thanks in advance!
Login
FAQ
Search