help on inner join / subselect using Criteria objects

[Derek Stevenson <de...@retrocode.com>]

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:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>