Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version:
3.2.1 GA
Mapping documents:
N/A
Code between sessionFactory.openSession() and session.close():
N/A
Full stack trace of any exception that occurs:
No Exception
Name and version of the database you are using:
Oracle 10g
The generated SQL (show_sql=true):
See below
Debug level Hibernate log excerpt:
N/A
I have been racking my brain over a particular filter issue I have been trying to work through for a while now. We're extensively using filters within our application to address requirements around tracking temporal data. I'll try to keep our overall arch short, see the following link for a more detailed description
http://forum.hibernate.org/viewtopic.ph ... t=temporal
Essentially for any of our data that is temporal we have a header table with a version table hanging off of it that contains one or more versions throughout time. In our case if we look for a record in a time period that doesn't have a version then for all intents and purposes that record 'doesn't exist' and will return null.
What we are doing right now is using a Class Level filter with a subselect in order to filter out records that don't have a corresponding version record for the query in question.
However, as we are continuing our usage of filters we are beginning to find cases where 2 individual subselects would need to know about each other.
I'll give an example:
Code:
from
AGMT_CNTRCT_LINE this_
where
this_.AGMT_CNTRCT_LINE_ID IN (
SELECT
version.AGMT_CNTRCT_LINE_ID
FROM
AGMT_CNTRCT_LINE_V version
WHERE
this_.AGMT_CNTRCT_LINE_ID = version.AGMT_CNTRCT_LINE_ID
AND version.CREATE_DATE = (
SELECT
MAX(b.CREATE_DATE)
from
AGMT_CNTRCT_LINE_V b
WHERE
version.AGMT_CNTRCT_LINE_ID = b.AGMT_CNTRCT_LINE_ID
AND ? < b.VERSION_END_DATE
AND ? > b.VERSION_START_DATE
)
AND version.ACTIVE_FLG=?
)
and this_.AGMT_CNTRCT_LINE_ID IN (
SELECT
version.AGMT_CNTRCT_LINE_ID
FROM
AGMT_CNTRCT_LINE_V version
WHERE
this_.AGMT_CNTRCT_LINE_ID = version.AGMT_CNTRCT_LINE_ID
AND ? < version.INACTIVE_DATE
AND ? > version.EFFECTIVE_DATE
)
In this case I am looking for the most recent version that is active and has a business effectiveness between 2 dates. However, this gives me the incorrect answer since the 2nd subselect isn't filtered down to the most recent version.
So I have a few options that I can think of:
-Add another permutation for the 1st subselect that contains the criteria for the effective, inactive date. However, our filters are dynamic based on the type of query we're doing so I end up having to specify a filter with and without the 2nd subselect incorporated. This is less than ideal. And in fact, already done with the active_flg. We'll be adding more scenarios like this so I can see this quickly spiraling out of control.
-Force an Inner Join to the Collection for the AGMT_CNTRCT_LINE_V table in the initial search. This actually removes the need to do the class level filter as we also have a collection level filter that gives us the right result. However, this quickly falls apart when you start loading the root level objects as collections off of other Entities.
Is there some way to tell the hibernate mapping to ALWAYS apply the inner join so that the filtering would apply automatically? This would be ideal as I could get rid of the class level filter altogether. Guess would also require the ability to give the join a specified alias so that it could be used in the query itself.
-Possibly change my mapping so that instead of a one to many collection I map it some other way that would give me the desired result. Although, I have no idea if possible or what it would be?
Does anyone have any insight to offer?
Login
FAQ
Search
