(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:



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.

If you must use join, then that's your only option, as far as I'm aware.

If you can change to a collection or an association (one-to-one, many-to-one, etc.), then you can use polymorphism and discriminator support, or the where="" clause.

One of my apps had a slightly more complicated version of the same problem. I chose to use <set where=""> mixed with <subclass discriminator="">. It means that I had to create several java classes that are used only in the mapping, but it works very cleanly. To extrapolate from your example, even though my top-level class has getMainContacts, getPotentialContacts, and getHistoricalContacts methods that all return instances of the Contact interface, I had to create a MainContactImpl class, a PotentialContactImpl class and an HistoricalContactImpl class.

Thanks for the response. I've actually used a similiar approach to the one you're describing for a different case. But in this case I wanted a more dynamic approach because the roles tend to change more. I actually got a new requirement yesterday: There's a need for a method named getBuyers(), but buyers are found using (role_id = 4 OR role_id = 31).

From what I understand, I can't use a set because I need to use the join table containing the involvements. But is there any reason there can't be a where="" attribute on the <join>?

Well, the join tag becomes a SQL join, and the syntax of SQL joins is:There's no where clause in there, unless expression includes a subselect. So that would be the reason that there's no where attributes on the join element.

Obviously I don't know your scema or POJO domain, but by and large you can always substitue an association (simple or collection) for a join. If you want the "parent" object to look like a single simple object, then you can use delegation.

I guess I was being a bit unclear. As you say, there's no where clause. But there is the on clause, which can contain arbitrary join condititions. What I want to do is as simple as adding role_id = 23 to the ON condition. It's trivial to do in SQL, so it's surprising that's it's so hard to do in Hibernate.

It's not really well supported because it's unnecessary. As far as your DB is concerned, these two statements are equally efficient:The second option is easily handled by Hibernate.

After some reconsiderations I decided to map all project involvements as the involvements property on the projects, and then use HQL/Collection filtering to implement the getContact() and getBuyers() methods. Something in my was struggling against this approach because I was being too SQL-centric, but it works quite well.