[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-298) allow joins in filters

[Shawn Clowater (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-298?page=comments#action_25768 ] 

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.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira