(This is a legacy database problem)
I have three tables,
project
company
project_involvement
The project_involvement table joins projects and companies, so it contains project_id and company_id.
But it also contains role_id, because companies can be involved with a project in various ways. One particular way of being involved is being the "main contact". This means the role_id in project_involvement is 23. For projects I want a method call getContact(), so I want to map the contact property in the project:
My current mapping for Project looks like this:
Code:
<join table="pro_involvement" subselect="select * from project_involvement where role_id = 23" optional="true" >
<key column="project_id"/>
<many-to-one name="contact" column="company_id"/>
</join>
I'm not thrilled about this subquery approach, as it is really not needed, adding a constraint to the join is a simple straightforward solution.
The manual contains something similiar:
http://www.hibernate.org/hib_docs/v3/re ... oc-complex
But it's not quite the same. I haven't been able to use the <properties> and property-ref trick because the role_id is in the join table and not in the company or project table.
What I need is some way of adding a join condition to the join, but how to do this is not immediately obvious to me. I have several similiar cases in this database, so I would be very happy if a more elegant solution could be found.