Hi guys,
I´ve run into some difficulties with historical records in the same table as active records. It seems to me that I have been thru every point in the documentation in search for a sollution; filters, @Where, and @Loader without results. (@Loader because a) I don´t like the concept of hardcoding more SQL than necessary, and b) @Loader doesn´t work when other entities is joined against my entity)
Let me just describe you the problem:
My table contains the following columns:
- id, int(10)
- validFrom, datetime
- validTo, datetime
- rowStatus, enum('active','inactive','deleted')active
- data, varchar(120)
The table contains all changes made to the entity, which is illustrated by the following rows:
Code:
id validFrom validTo rowStatus
1 2009-01-26 16:31:09 NULL active
1 2009-01-26 16:32:05 NULL active
What I´m trying to achive is that Hibernate generates SQL which would look like this:
Code:
SELECT *
FROM entity_table o
WHERE o.id = 1
AND o.validFrom = (
SELECT MAX(i.validFrom)
FROM entity_table i
WHERE o.id = i.id
AND i.validFrom <= NOW()
AND (
i.validTo >= NOW()
OR i.validTo IS NULL
)
)
AND o.rowStatus = 'active'
The problem is that I don´t know the alias of the outer table which makes it impossible to preform the correct subselect. I can get Hibernate to return me all rows regardless of the latest validFrom (without subselect) and the globally newest row (without o.id = i.id) - but I simply cant get it right :-(
Anyone up for the fight? Thanks in advance...
-- Brian
Login
FAQ
Search