Adjusting the Outer Join Predicate using Criteria Query

bengrant · **Joined:** Wed Sep 13, 2006 7:24 am **Posts:** 26

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

Quote:

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

Quote:

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

How can I add to the join predicate, as I need to filter the table before joining.

batmat · **Posted:** Fri Dec 08, 2006 11:48 am

What is the code used to create your criteria query ?

bengrant · **Joined:** Wed Sep 13, 2006 7:24 am **Posts:** 26

batmat wrote:

What is the code used to create your criteria query ?

Code:

Criteria tabA = session.createCriteria(TableA.class);
tabA.createAlias("Table_B"," Table_B", Criteria.LEFT_JOIN).add(Restrictions.eq("Talbe_B.Col_3", ACTIVE_REGION));
tabA.setProjection(
                   …
                   …
                    );

List list =tabA.list();

batmat · **Posted:** Fri Dec 08, 2006 12:26 pm

bengrant wrote:

batmat wrote:

What is the code used to create your criteria query ?

Code:

Criteria tabA = session.createCriteria(TableA.class);
tabA.createAlias("Table_B"," Table_B", Criteria.LEFT_JOIN).add(Restrictions.eq("Talbe_B.Col_3", ACTIVE_REGION));
tabA.setProjection(
                   …
                   …
                    );

List list =tabA.list();

What does it give if you put the Restrictions part at the end ? Say :

Code:

Criteria tabA = session.createCriteria(TableA.class)
   .createAlias("Table_B"," Table_B", Criteria.LEFT_JOIN)
   .setProjection(
                   …
                   …
                    )
   .add(Restrictions.eq("Talbe_B.Col_3", ACTIVE_REGION))

bengrant · **Joined:** Wed Sep 13, 2006 7:24 am **Posts:** 26

batmat wrote:

bengrant wrote:

batmat wrote:

What is the code used to create your criteria query ?

Code:

Criteria tabA = session.createCriteria(TableA.class);
tabA.createAlias("Table_B"," Table_B", Criteria.LEFT_JOIN).add(Restrictions.eq("Talbe_B.Col_3", ACTIVE_REGION));
tabA.setProjection(
                   …
                   …
                    );

List list =tabA.list();

What does it give if you put the Restrictions part at the end ? Say :

Code:

Criteria tabA = session.createCriteria(TableA.class)
   .createAlias("Table_B"," Table_B", Criteria.LEFT_JOIN)
   .setProjection(
                   …
                   …
                    )
   .add(Restrictions.eq("Talbe_B.Col_3", ACTIVE_REGION))

Nope, Hibernate must be cleaver as it came up with the same SQL ... :(

bengrant · **Joined:** Wed Sep 13, 2006 7:24 am **Posts:** 26

is there a place where i can request this to be added to Hibernate ?

going thru the code, it looks like it doesn't handle additions to the join caluse?

batmat · **Posted:** Wed Dec 13, 2006 1:02 pm

bengrant wrote:

is there a place where i can request this to be added to Hibernate ?

Yes, sure. I think this is the place to speak about what you think is a bug : at least, you'll know if it's a bug or something else. :-) Try http://opensource.atlassian.com/project ... board.jspa

As I'm sure you're aware, please search the already present bugs before filing a new one :).

xerces8 · **Joined:** Thu Oct 19, 2006 12:07 pm **Posts:** 75

Any solution yet ? I have the exact same problem.