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

[Scott Van Wart (JIRA)]

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

Scott Van Wart commented on HHH-2308:
-------------------------------------

Theo,

> If you enable a filter on a collection it will add it as an outer join predicate.

With the criteria API it seems to add it to the WHERE clause instead, which is no good, because then left outer joining is useless.  Consider this (SQL, you sound like you probably already know all about this, but I'll include it for the sake of clarity :) ):

  SELECT p.name, c.name FROM parent p LEFT JOIN child c ON p.parentId = c.parentId AND c.gender = 'M';

This will return ALL parent names.  It will also return all male child names, or NULL if the parents don't have any male children.

  SELECT p.name, c.name FROM parent p LEFT JOIN child c ON p.parentId = c.parentId WHERE c.gender = 'M';

All we've done is moved the predicate to the WHERE clause, but this will exclude all parents who only have female children.  In my case the results are 2000 rows with the extra join predicate versus a few hundred thousand if I have to filter after retrieval.  No good :(.  Patch time.

> Adjusting the Outer Join Predicate using Criteria Query
> -------------------------------------------------------
>
>                 Key: HHH-2308
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2308
>             Project: Hibernate Core
>          Issue Type: New Feature
>          Components: query-criteria
>    Affects Versions: 3.2.1
>         Environment: Linux Using MS SQLServer
>            Reporter: Ben Grant
>         Attachments: hibernate-joinOn-sorted.ZIP, hibernate-joinOn.ZIP
>
>
> 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