I have an strange relation among several tables.

There are 3 main tables PERSON, ROLE and CONTEST. Each has an autonumeric PK. Names are IDPERSON, IDROLE, IDCONTEST

Originally the application had a relation table called ROLECONTEST:
PK = IDROLECONTEST
FKs = IDROLE, IDCONTEST
Other data

Another relation table that related ROLECONTEST and PERSON called PERSONROLECONTEST
PK = IDROLECONTEST
FKs = IDROLECONTEST, IDPERSON
Other data

Later I needed to add a direct relation between PERSON and CONTEST, so I created PERSONCONTEST
PK = IDPERSONCONTEST
FKs = IDCONTEST, IDPERSON
Other data

The ideal solution would be to have 3 FKs in PERSONROLECONTEST to PERSON, ROLE and CONTEST. I that case the thing would be easy. But it is an application in production stage with a lot of data, and I prefer not to make such a massive change. Now I have the problem on how to relate PERSONROLECONTEST and PERSONCONTEST in hibernate.

The relation from PERSONROLECONTEST to PERSONCONTEST was easy:

In the mapping of PERSONCONTEST I declared a propeerties tag that comprises 2 columns:

<properties name="personcontest">
<many-to-one name="person" class="Person" column="IDPERSON" lazy="proxy" fetch="select" />
<many-to-one name="contest" class="Contest" column="IDCONTEST" lazy="proxy" fetch="select" />
</properties>

And in PERSONROLECONTEST I used a formula for the many-to-one relation:



The problem is that I have no idea on how to make the inverse relation. I need to recover a collection of PersonRoleContest (A set is preferred) using a formula, but I saw no way on how to do that. I know how to use formulas on calculated fields, but not to recover a set.

As a summary, I need to map the relation mapped with >>>>>>>. I have a PERSONCONTEST, and need to get a set of ROLEPERSONCONTEST. The query would be

SELECT rpc.IDROLEPERSONCONTEST
FROM ROLEPERSONCONTEST rpc
LEFT JOIN ROLECONTEST rc ON rc.IDROLECONTEST = rpc.IDROLECONTEST
WHERE rpc.IDPERSON = IDPERSON AND rc.IDCONTEST = IDCONTEST