I've searched both this forum and google for a long time today, and I just have no clue how to solve my problem.

The problem is, we've got an application where we have no control over the database structure. It is what it is.

I have one table (let's call it DET) with a column for diagnostic type (DIG). That DIG code is tied to a reference table (let's call it DIG_CDS).

So, in the DET.DIG column, we can have a value such as '7330' (Yes, it's a string). However, in our reference code table, DIG_CDS, we've got multiple rows identified by DIG. the DIG_CDS table has additional columns for Version and effective start/end dates. Basically, I need to find the highest version number where the start/end dates surround another date on a parent table to DET.

I've tried mapping subselects, filters, functions, and nothing gets me to where I need it.

Is there anything I can do with interceptors or event listeners before or during the load to manipulate the query?

When I try to load the DET object it is fine, but once I try to lazy load the diagnosis code:

I get the expected error of:

I realize this database design isn't proper, but we cannot change the db structure. Is there anything we can do to load the DiagnosisType? I can load lists of Diagnosis Types, and I can even save Diagnosis types with values that are either unique or duplicated in the reference table. It's just loading an individual value for on-screen display is dying.

Thanks

So, 7330 isn't a primary key? Is there a way to make a record unique in the associated table through a compound key? Then use the compound key as the foreign key?

I've seen these types of things before. I call them 'mystery keys' because they are a mystery as to how anyone who designed the database every thought they could work.

You really need a proper foreign key. This must be an old database, because all foreign keys should have a constraint, just for the sake of integrity.

I'd remove the association, and then just do a separate query for the record. This design means your mapping isn't really correct, and that's not something you want to put into production.

Nope, 7330 isn't a PK, but the diagnosis code table does have a version column on it so we could use a composite key containing DIG_CDS.DIG and DIG_CDS.VER columns.

Problem is, the DET table references DIG_CDS with just a single column, DIG, so we can't store which version is being used.

Breaking the association in the mapping would definitely work, it's so simple I can't believe I didn't think about it, but you're right, this isn't good stuff to do. It doesn't help that this is one of at least 10 tables with the same problem, and we'd have to change a number of places that rely on lazy loading.

Actually, I could break the mapping, then write an event listener which would trigger after the load, and populate these dangling 'mystery keys'...

The reason we don't have foreign keys, is that our table is the DET table, and we're using an alias to the DIG_CDS table which is a shared/global table for use by any application, because of how security is done where I work. Because of this they don't/can't/won't have FKs to the shared tables. It's messy, and I'm not a fan one bit. I'm tempted to just say "hey forget the shared table, lets make our own table, and just load it from the shared table, but at least give it some true UID's or something" but that would have to wait until a future release.

Thanks.