I've got a many-to-many association, and I'm not sure where to start. I'm using someone else's database schema that looks like the following:

Foo
int id <--- primary key
int val <---- Second unique key
String Name

Bar
int identifier
String Name

MappingTable
int identifier <----Maps to Bar.identifer
int secondId <---- Maps to Foo.val (!) under the right conditions
int type <---- That's the condition. type must == 1 for a Foo to Bar mapping.

This schema is more complex than I would have made, but allows for the MappingTable to map Bar to more than one table (Foo, Foo1, Foo2) all with int identifiers.

My question is can I produce a Collection mapping that will allow me to one-way map Foo join Bar where MappingTable.type==1? I don't have much control over the schema, but might be able to make a case for multiple association tables. I'm a "simple-as-possible" database guy, so the type in the MTM mapping table isn't my style, but I can certainly understand how it might be someone else's.

The association is so confusing to me that I haven't successfully put a mapping together that makes sense to me. The foreign key value I'm using isn't the primary key for the table, so it's throwing me for a loop.

If it was joining the PK, I'd just use a set


But it isn't joined using the PK, and all the wishing in the world won't make it so.

Any suggestions would be greatly appreciated although there's almost no chance of a schema change.