[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2308) Adjusting the Outer Join Predicate using Criteria Query

[Theo Platt (JIRA)]

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

Theo Platt commented on HHH-2308:
---------------------------------

I've just come across the same problem and this issue has my vote.

You can get around the problem by using setting a result transformer of CriteriaSpecification.DISTINCT_ROOT_ENTITY on your criteria but its clunky as the database may actually fetch a lot more results than you eventually need.

However with this solution another problem occurs if you're trying to create a paged result set and setting the FirstResult and MaxResults to the 'page' of results you want to see. The result transformer may return less results than you need to fill your page.

Then comes a problem with producing a total results count query by doing a rowcount projection - as the distinct root entity result transformer to the count query as I imagine it has no idea what the root entity is - it just counts all the results.

Theo

> Adjusting the Outer Join Predicate using Criteria Query
> -------------------------------------------------------
>
>                 Key: HHH-2308
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2308
>             Project: Hibernate3
>          Issue Type: New Feature
>          Components: query-criteria
>    Affects Versions: 3.2.1
>         Environment: Linux Using MS SQLServer
>            Reporter: Ben Grant
>
> I have two tables 
> Table A 
> ||Col_1||Col_2|| 
> |London| UK | 
> |Liverpool| UK | 
> | New York | USA | 
> Table B 
> ||Col_1||Col_2|| Col_3|| 
> | UK | Europe | 0 
> | USA | Americas | 1 
> Using the Criteria class, Restriction Class and FetchMode, Hibernate manages to create a query that looks like this 
> select distinct top 2000 
> this_.Col_1 as y0_, TableB3_.Col2 as y1_ 
> from TableA this_ 
> left outer join TableB TableB3_ on this_.Col_2= TableB3_.Col_1 
> where TableB3_.Col_3=1 
> When really i need the query to be like this 
> select distinct top 2000 
> this_.Col_1 as y0_, TableB3_.Col2 as y1_ 
> from TableA this_ 
> left outer join TableB TableB3_ on this_.Col_2= TableB3_.Col_1 AND TableB3_.Col_3=1 
> currently their isn't any know way for hibernate to adjust or apply filters within the join clause.

-- 
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