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

[Frederic Leitenberger (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-298?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_26829 ] 

Frederic Leitenberger commented on HHH-298:
-------------------------------------------

Hello,

is there something new on this issue?
I need the same function as Shawn Clowater described.

I am handling those filters with subselects in an mostly inefficient way.
Our new version is near completion and will be released soon.
I would like to remove those sub-querries before it is released.

I think i don't need to explain the performance difference between a Join-Select and multiple recursive sub-querries.

What we try to do is an access-filter for resellers.

1. When a "resellerFilter" is activated only entities associated to the given resellers should be retrieved.
Reseller:
@Filter(name = "resellerFilter", condition = "id in (:resellerIds)")

2. First association
Reseller.Customer:
@Filter(name = "resellerFilter", condition = "resellerId in (:resellerIds)")

3. On the first associaten-level this looks still simple, but is not optimal anyway:
Reseller.Customer.GeoNumber:
@Filter(name = "resellerFilter", condition = "(select cu.resellerId from Customer cu where cu.id = customerId) in (null, :resellerIds)")

4. In This case we have to possible relation-paths for the entity deteme and then it starts getting really ugly and slow ...
Reseller.Customer.DeTeMe OR Reseller.Customer.GeoNumber.DeTeMe:
@Filter(name = "resellerFilter", condition = "(select cu.resellerId from Customer cu, GeoNumber gn where (cu.id = gn.customerId and gn.id = this_.geoNumberId) UNION select cu.resellerId  from Customer cu where cu.detemeTemplateId = this_.id) in (null, :resellerIds)")

Maybe this can be resolved in Criteria-Style using the internal Criteria functions already implemented. Maybe like this:
1. Reseller
@FilterCriteria(
	alias="this",
	path=null,
	restrictions = {
		@Restriction(type=RestrictionType.ID_IN, value=":resellerIds")
	},
	criterions={}
}

2. Reseller.Customer
@FilterCriteria(
	alias="this",
	path=null,
	restrictions = {
		@Restriction(type=RestrictionType.IN, name="this.resellerId", value=":resellerIds")
	},
	criterions={}
}

3. Reseller.Customer.GeoNumber
@FilterCriteria(
	alias="this",
	path=null,
	restrictions = {},
	criterions={
		@FilterCriteria(path="this.customer", alias="cu", restrictions = {
			@Restriction(type=RestrictionType.IN, name="cu.resellerId", value=":resellerIds")
		}
	}
)

4. Reseller.Customer.DeTeMe AND Reseller.Customer.GeoNumber.DeTeMe
@FilterCriteria(
	alias="this",
	path=null,
	restrictions = {},
	criterions={
		@FilterCriteria(path="this.customer", alias="cu", restrictions = {
			@Restriction(type=RestrictionType.IN, name="cu.resellerId", value=":resellerIds")
		},
		@FilterCriteria(path="this.geoNumber.customer", alias="gn", restrictions = {
			@Restriction(type=RestrictionType.IN, name="gn.resellerId", value=":resellerIds")
		}
	}
)

And for "OR"-support:
@Restriction(type=RestrictionType.OR, restrictions={
	@Restriction(...),
	@Restriction(...)
})

> allow joins in filters
> ----------------------
>
>                 Key: HHH-298
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-298
>             Project: Hibernate3
>          Issue 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