Hello, Anyone got any further insight into this?
Thanks, Derek At 01:04 PM 9/25/2002, Derek Stevenson wrote: >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]> > -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
