Querying join table

[Fredrik Liden <fl...@translate.com>]

I'm trying to figure out two queries from the following structure.

SURVEY
SurveyID  (PK)

PERSON_SURVEY            
PersonID (PK,FK)
SurveyID (PK,FK)

PERSON       
PersonID (PK)


1. I have a join table PERSON_SURVEY with a composite key of PersonID
and SurveyID. How can I query for all the entries where PersonID =
'something'. Do I use an expression or a path? Since this is a composite
key and is part of the PK. In general, is it preferred to stay away from
composite keys in the join tables?

The second one is: Is there any way I can formulate a query that returns
all the entries in the PERSON table that are not mentioned in the
PERSON_SURVEY table for a specific survey. In other words, I'd like to
get a list of people that haven't been assigned anything yet. I'm having
complete brain freeze.

If anyone could spare some time to take a look that would be awesome.

Fredrik

Re: Querying join table

[Mike Kienenberger <mk...@gmail.com>]

On 5/3/06, Fredrik Liden <fl...@translate.com> wrote:
> I'm trying to figure out two queries from the following structure.
>
> SURVEY
> SurveyID  (PK)
>
> PERSON_SURVEY
> PersonID (PK,FK)
> SurveyID (PK,FK)
>
> PERSON
> PersonID (PK)
>
>
> 1. I have a join table PERSON_SURVEY with a composite key of PersonID
> and SurveyID. How can I query for all the entries where PersonID =
> 'something'. Do I use an expression or a path? Since this is a composite
> key and is part of the PK. In general, is it preferred to stay away from
> composite keys in the join tables?

Yes, I recommend using a composite join table.   Cayenne has excellent
support for this situation.  Make sure to mark the reverse
DbRelationships as "to dep key".   You'll probably need to manually
add ObjRelationships to your Person and Survey ObjEntities.

Once you've configured everything correctly, your join tables will be
invisible to your application.   You'll be able to use
person.addToSurveyList() and person.getSurveyList() and
survey.addToPersonList() and survey.getPersonList() transparently.