Hi, all. I have what has turned out to be a rather frustrating problem to solve, and hope that
someone out there can help.
I have two classes, Parent and Child, which are both versioned in a
rolling audit table and contain an ID and CHANGE_ID comprising the composite primary keys of each.
The problem is that the Child records do not have to be updated with the Parent, leaving me with
a situation where the correct method of determining a Child's CHANGE_ID for any given Parent CHANGE_ID is:
Code:
MAX(child_change_id) WHERE child_change_id <= parent_change_id
Fun, huh?
This means that given the following classes:
Code:
class Parent {
private CompositeId id; // comprised of PARENT_ID and PARENT_CHANGE_ID
private Child child;
}
class Child {
private CompositeId id; // comprised of CHILD_ID and CHILD_CHANGE_ID
}
...and the following tables:
Code:
table Parent (
int parent_id;
int parent_change_id;
int child_id;
)
table Child (
int child_id;
int child_change_id;
)
...and the following records:
Code:
PARENT_ID PARENT_CHANGE_ID CHILD_ID
123 1 456
123 2 456
...
CHILD_ID CHILD_CHANGE_ID
456 1
456 3
...
...the Child record with CHILD_CHANGE_ID=1 needs to be returned for
both versions of the Parent
record 123.
This is easily done for the cases where the PARENT_CHANGE_ID and CHILD_CHANGE_ID records match up:
<many-to-one name="child" class="Child">
<column name="CHILD_ID"/>
<column name="PARENT_CHANGE_ID"/>
</many-to-one>
However, for the other case, I can't seem to figure out exactly how to map the association. A
simple formula can give me a property of Parent containing the correct version of the Child record
to return...
<property name="correctChildVersion"
formula="(SELECT MAX(CHILD_ID) FROM Child c where c.CHILD_ID=CHILD_ID and c.CHILD_CHANGE_ID <= PARENT_CHANGE_ID)"/>
...but I haven't discovered the key to utilizing the propery in the composite key, ala:
<many-to-one name="child" class="Child">
<column name="CHILD_ID"/>
<!-- what do I put here? -->
</many-to-one>
...or, quite possibly, a more
appropriate solution to the problem.
Any assistance would be greatly appreciated...hell, at this point I'll buy you a beer.
Login
FAQ
Search