Mapping a join using collection-descriptor, not joining on primary key

[Patrick Prodhon <pa...@sap.ap-hop-paris.fr>]

Hello.

I'm currently trying to make a join between two tables using a
collection-descriptor.
Here's the story : I've got a table listing units, with a primary key
consisting of a unit ID. Each unit can have a manager, and a correspondant :
those are two 0:1 relations (you can have one or none in each case). So the
units table has got two columns each containing an ID which points to the
persons table.
Here is the SQL request I'd issue :
SELECT *
FROM UNITS U
LEFT OUTER JOIN PERSONS P1 ON U.MNG_ID = P1.PID
LEFT OUTER JOIN PERSONS P1 ON U.CORR_ID = P2.PID;

For the moment, I have tried with a reference descriptor, on one column only
:
<reference-descriptor
  name="manager"
  class-ref="mypkg.Manager"
  auto-retrieve="true"
>
   <foreignkey field-id-ref="2" />
</reference-descriptor>

The second field is MNG_ID. The only problem is that, using P6Spy, I noticed
that each time I called the returned Iterator's next() method, OJB would
issue a call to the database to retrieve the data from the PERSONS table,
which certainly isn't what I want : I want the whole data to be retrieved in
one SQL call, period.
So I glanced into the documentation, to see the "allArticlesInGroup"
example. But this isn't quite what I want : I'd like to be able to specify
that I want MNG_ID to be used for the join, not USERS' primary key. And
since the collection-descriptor should be on the USERS table and it doesn't
permit to specify a "foreign-key", I don't know how I can do it.

Can I achieve this and have OJB get the job done in one SQL call ?

Thanks.
Patrick.