[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-298?page=co... ] 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(...) }) -- 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