HQL question

fyang1024 · **Joined:** Tue Aug 08, 2006 10:22 pm **Posts:** 5

I have 5 tables/classes:

User(userid, name),
Organisation(orgid,name),
Role(roleid,name),
UserOrganisation(uoid,userid,orgid), //user membership in organisation
UserOrganisationRole(uorid,uoid,roleid) //user roles in organsation

I have a javaBean called UserBean(userid,name,Role[])

How can I use HQL to compose a query to get a list of UserBeans from an Organisation given the orgid?

Thanks

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Code:

select u
from UserOrganisation uo
join uo.User u
where uo.Organisation.id = :orgid

fyang1024 · **Joined:** Tue Aug 08, 2006 10:22 pm **Posts:** 5

tenwit wrote:

Code:

select u
from UserOrganisation uo
join uo.User u
where uo.Organisation.id = :orgid

But where are the users roles in that organsiation?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Hibernate will return a list of Users. Presumably you have set up User to contain a collection of Roles (via the UserRoles join table). So user.getRoles() will return the collection of a user's roles.

Remember, Hibernate is an ORM. It doesn't load database rows, it loads objects. If objects contain references to other objects, hibernate loads those, too. You don't need to tell it to, that's its raison d'etre. You can't get it to not do that.

fyang1024 · **Joined:** Tue Aug 08, 2006 10:22 pm **Posts:** 5

tenwit wrote:

Hibernate will return a list of Users. Presumably you have set up User to contain a collection of Roles (via the UserRoles join table). So user.getRoles() will return the collection of a user's roles.

Remember, Hibernate is an ORM. It doesn't load database rows, it loads objects. If objects contain references to other objects, hibernate loads those, too. You don't need to tell it to, that's its raison d'etre. You can't get it to not do that.

Not really that case. In our application, User's Roles are different in different Organisations. So there's no User.getRoles() at all. Instead, User.getUserOrganisations() to get membership and UserOrganisation.getUserOrganisationRoles() to get Roles of that user in that organisation.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Then you can return the correct UserOrganisations from the query, and get the Roles and User from that:

Code:

from UserOrganisation where Organisation.id = :orgid

Or you could get a list of [User, Role] arrays:

Code:

select u, r
from UserOrganisation uo
join uo.Role r
join uo.User u
where uo.Organisation.id = :orgid

This will return one [User,Role] per Role, so the same User will be returned multiple times, or none if it has no roles. You could make the join to Role be a left join, that will return a [User, null] for Users with no Roles in the UserOrganisaiton.

I am assuming that you are not mapping UserRole explicitly, and just specifying it as a join table.

fyang1024 · **Joined:** Tue Aug 08, 2006 10:22 pm **Posts:** 5

tenwit wrote:

Then you can return the correct UserOrganisations from the query, and get the Roles and User from that:

Code:

from UserOrganisation where Organisation.id = :orgid

Or you could get a list of [User, Role] arrays:

Code:

select u, r
from UserOrganisation uo
join uo.Role r
join uo.User u
where uo.Organisation.id = :orgid

This will return one [User,Role] per Role, so the same User will be returned multiple times, or none if it has no roles. You could make the join to Role be a left join, that will return a [User, null] for Users with no Roles in the UserOrganisaiton.

I am assuming that you are not mapping UserRole explicitly, and just specifying it as a join table.

I appreciate your help.

Finally, based on your query, I came up with the following:

Code:

select u.userId,u.login,u.title,u.firstName,u.lastName, r from User u left join u.userOrganisations as uo left join uo.userOrganisationRoles as uor left join uor.role as r where uo.organisation.organisationId=?

Then I created the UserManageBean(userId,..., List<Role>) list from the list returned.

And it does work perfectly!

Thanks