You can do this by having a many to many association between users and groups as follows, assuming you have User, Group and Group_User tables, where Group_User is a link table, you can avoid n+1 selects by having the following mapping in User.hbm.xml <set name="groups" outer-join="true" table="Group_User"> <key column="user_id"/> <many-to-many column="group_id" outer-join="true" class="Group"/> </set>
Note if you have outer-join="true" only as an attribute of the set element it will eager fetch only the rows from the Group_User table and will result in n+1 selects as you have described below, to get the groups. But when you add outer-join="true" as an attribute in the many-to-many element, it will fetch the group instances too by fetching rows from Group table in the same query. Along with the above you need to set the hibernate property hibernate.max_fetch_depth=2 (default is 1)as hibernate has to traverse two levels to get the groups. Now the down side is that this outer join query will be run each time a user instance is loaded. You might encounter performance problems if you are not using scalar queries to get a list of users. Hope this helps Arvinder vegarwe wrote: > > I'm working on project making a website where we use hibernate, spring and > maven much like Appfuse. Although we didn't start out using Appfuse I know > wish we did. The project has been running for a year and a half and we > have had 4 releases with the latest one running on our site at all times. > > We designed the architecture as a three-tier mvc application and even > though it doesn't use the same names (as Appfuse) it easily translates > into the package name space of appfuse. (I'm going to do the translation > in this post so the names are familiar). > > Simplified we have these classes and relations: > * example.dao.UserDao > * example.dao.GroupDao > * example.model.User > * example.model.Group > * example.service.UserManager > * example.service.GroupManager > > where User and Group has a many-to-many relation. > > This is all working to plan and has served us well. Our problem is that we > now wants to add history to our group membership so we want to defined a > example.model.GroupUser which has membershipFrom and membershipEnd. The > correct way to solve this is to add the new object and turn the > many-to-many relation between groups and users into two on-to-many > relations (User -> GroupUser, Group -> GroupUser). Am I right? > > Now, all the existing code fetches a user object (u) and says > u.getGroups() to fetch corresponding groups. By changing the relation this > function is replaced by a u.getGroupUsers() which hold the relation > object, but not the actual group itself. To get the group (any group) you > have to call u.getGroupUsers().get(0).getGroup(). I have tried this, and > it works fine. I can even create the function > example.model.User.getGroups() to iterate over GroupUsers, fetch groups > and add them to a return arraylist. But unless hibernate is exceptionally > smart her, I will end up doing n+1 queries to the database (one to fetch > GroupUsers and one for each entry to fetch the group) when all I need is > one query. (Yes this makes a difference). > > Does anybody has a suggestion for how to solve this? I really don't want > to break the existing code so I need the u.getGroups() to work as it did > before, but I don't want a n+1 query either. I thought about defining both > a one-to-many _and_ a many-to-many between User and Groups to get both > u.getGroups and u.getGroupUsers (...have it both ways), but that seems > like a hack to me. Oh, and of course I want u.getGroups() to fetch groups > where GroupUser.membershipEnd is null or in the future, but that I think > I'll be able to handle. > > I now this post was lengthy, but it was hard explaining it in less words. > Sorry about that. Hope everybody was able to understand what I'm looking > for here. > > -- > Vegar Westerlund > -- View this message in context: http://www.nabble.com/Getting-the-architecture-right-tf4081789s2369.html#a11631108 Sent from the AppFuse - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
