[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2308?page=c...
]
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira