Ryan, I did read the howtos, and what you suggest will work fine when selecting users from a particular group. However, what I'm trying to do is start with a given user, rather than a given group, find which groups s/he belongs to, then select all users who are in turn part of those groups, and select them uniquely. In other words,
User A --> part of Groups 1 and 2 --> Group 1 has Users A, B and D; Group 2 has Users A, C and D --> return unique list of users in Groups 1 and 2 --> list should contain Users A, B, C, D Ideally I'd like to do it in a single query as the raw SQL below does. I have a workaround now where I first select the Groups, get their primary keys, put them in an int[] array, which I then use in a second query via criteria.addIn(GroupPeer.GROUP_ID, groupIDs). However my question is, is there a more elegant way to set up the criteria object to mimic the raw SQL command below (which does return the proper results)? Thanks, Derek At 07:21 AM 9/25/2002, you wrote: >Assuming I understand you correctly, this is pretty trivial. Did you read >the Peers howto and the Criteria howto? > >Criteria criteria = new Criteria(); > >//do the joins >criteria.addJoin(UserPeer.USER_ID, UserGroupPeer.USER_ID); >criteria.addJoin(UserGroupPeer.GROUP_ID, GroupPeer.GROUP_ID); > >//limit to group A >criteria.and(GroupPeer.GROUP_NAME, "A"); > >//get a list of users satisfying the above criteria >Vector results = UserPeer.doSelect(criteria); > >It is often helpful to see the SQL log that Torque generates for you. See >Torque.properties to see where this log is. > >HTH, >Ryan > >-----Original Message----- >From: Derek Stevenson [mailto:[EMAIL PROTECTED]] >Sent: Tuesday, September 24, 2002 6:02 PM >To: Turbine Users List; [EMAIL PROTECTED] >Subject: help on inner join / subselect using Criteria objects > >I am trying to use the Criteria object to query a database and return a >distinct list of users which are in the same group(s) as a given >user. Here are my tables: > >USER >---- >USER_ID >FIRST_NAME >LAST_NAME > >USER_GROUP >---------- >USER_ID >GROUP_ID > >GROUP >----- >GROUP_ID >GROUP_NAME > >USER_GROUP is a join table, in which a user can be part of one or more >groups. Say for example user A is in group 1 and 2, user B is in group 1, >user C is in group 2, and user D is in groups 1 and 2. I want to use the >Criteria object to return a Vector of User objects a la >UserPeer.doSelect(criteria). Based on this sample data, I want to find all >users which belong to user A's groups. My result should contain users A, >B, C, and D, all occurring only once in my Vector. > >As a first pass, I put together a SQL query that would do the job. The >following will seemingly work fine to provide the desired list: > >select distinct c.* from USER_GROUP as a, USER_GROUP as b, USER as c where >a.USER_ID='A' and a.group_id=b.group_id and b.user_id = c.user_id; > >I'm having trouble translating this into something Criteria can use, >however. I first thought I might need to use an inner join or subselect, >although since we're using MySQL I don't think I can use subselects/inner >joins (maybe Criteria translates it into something mysql will >understand?). The query above does work against mysql, however. > >Do I need to use Criteria.addAlias to do table aliasing as I've done in my >query? If so, what is the proper syntax? Alternatively, I thought I might >have to use BasePeer.executeQuery() instead and pass it the above SQL query >directly. > >Any help would be greatly appreciated. > >Derek > > >-- >To unsubscribe, e-mail: ><mailto:[EMAIL PROTECTED]> >For additional commands, e-mail: ><mailto:[EMAIL PROTECTED]> > >-- >To unsubscribe, >e-mail: <mailto:[EMAIL PROTECTED]> >For additional commands, e-mail: ><mailto:[EMAIL PROTECTED]> -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
