10:24 p.m.
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-298?page=co...
]
Shawn Clowater commented on HHH-298:
------------------------------------
I've got no problem doing the work since I'm really the one asking for the
functionality, viva open source.
However, I do have some questions on how you foresaw this working. In the simple example
you provided you do a simple inner join to a child table. However, how were you going to
inject the join table into the FROM clause (and ensure that it doesn't conflict with
the initial query).? Without the table in the FROM clause I would assume that the sql
parser wouldn't know what the alias is and throw an exception.
In my case I really only simply needed to force an inner join on a non-lazy loaded
collection so that it would be added to the FROM clause and then the collection filter
actually handles the rest very nicely. (and in this case I wouldn't even need the
filter at the entity level). However, this original functionality enhancement also
seemed like a good way to achieve the same thing and seems a bit less exotic.
What I'm trying to accomplish for my case is to correlate multiple filters at the
entity level that end up being tackled with subqueries.
I'm essentially trying to get the generated SQL to go
From:
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
)
To:
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 ? < version.INACTIVE_DATE
AND ? > version.EFFECTIVE_DATE
)
Where the INACTIVE/EFFECTIVE DATE piece is a separate filter. The filters working
together should give the record that has a version that is the most recently created that
fall within that start/end date, is active and falls within the inactive/effective date.
In the first case I can actually return something as long as it's most recent version
is active and falls within the start date and then has at least one version that was
effective at some point (might be a different version). When the collection is loaded the
the collection filter is applied as in the 2nd case and then we end up with an Entity that
has no versions which is what we were trying to filter against.
So, barring any snarls with trying to inject the joined table back into the FROM clause,
what do you think of the following? Having some sort of nested construct that I could
apply the initial subselect and then the remaining filters would be applied against it. I
know it doesn't really address your original idea but it would reduce the number of
subselects I'm currently using and they'd actually work properly.
So essentialy I would end up with some construct that would generate the
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 then my subsequent filters would get applied to this fragment.
allow joins in filters
----------------------
Key: HHH-298
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-298
Project: Hibernate3
Type: Improvement
Components: core
Reporter: Steve Ebersole
Assignee: Steve Ebersole
For the sake of performance with complex filters, it'd be nice to allow users to
specify joins fragments to be added to the filter tags. Somthing like:
<filter name="myFilter">
<filter-join table="MyPermissionTable">
<condition>
{this}.id =
{MyPermissionTable}.object_id
</condition>
</filter-join>
<![CDATA[
{MyPermissionTable}.user_id =
:userId
AND
{MyPermissionTable}.perm_flg <
:userPermLevel
]]>
</filter>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira