Criteria & OuterJoin - 3.1rc2

lukjel · **Joined:** Mon May 23, 2005 5:42 pm **Posts:** 7

Hi!

I'm trying to search some records which mach "search-request".
Simple example: company has workers (one-to-many) and owners (one-to-many). Now I want to prepare some criteria like this:

Code:

crit.createAlias("owners", "o").setFetchMode("o", FetchMode.JOIN);
crit.createAlias("workers", "w").setFetchMode("w", FetchMode.JOIN);
Disjunction critPg = Restrictions.disjunction();
critPg.add(Restrictions.ilike("name", "%" + params[i]));
critPg.add(Restrictions.ilike("o.name", "%" + params[i]));
critPg.add(Restrictions.ilike("w.name", "%" + params[i]));
crit.add(critPg);
crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

What a supprise - I can't force hibernate to create query with outer join... The result is that I never received company which has no workers... Even if the name or owners mach.

Any ideas?
Is this a bug or feature?

Regards
Lukjel

lukjel · **Joined:** Mon May 23, 2005 5:42 pm **Posts:** 7

I've just missed some comments:

I've tried also with all combinations of FetchMode - with no effect.

Lukjel.

clcantrell · **Joined:** Mon Oct 24, 2005 2:51 pm **Posts:** 7

It seems any time I include a projection, the criteria API reverts to inner joins. I am trying to over come this also, but so far, no luck.

VampBoy · **Posted:** Mon Oct 24, 2005 3:50 pm

Criteria API does not support Outer Joins as of yet. Use the search and you'll find half a dozen threads discussing it.

clcantrell · **Joined:** Mon Oct 24, 2005 2:51 pm **Posts:** 7

The criteria API does support outer joins if you have not created a subcriteria on the root criteria. This is found in the CriteriaJoinWalker in the method getJoinType. If a subcriteria has been placed in the associationPathCriteriaMap then an inner join is returned. If the criteria is only on the root entity, then outer joins are returned for the fetchModes that have been set on the root criteria.

VampBoy · **Posted:** Mon Oct 24, 2005 6:15 pm

Yes, you can tell hibernate to fetch a collection using an outer join by setting the fetch mode but the createAlias (or createCriteria) only creates inner joins.

Also, if you troll the JIRA for outer join you will find that this is not seen as a bug.

Although, to the original poster, do you get any results if any of your other restrictions in your disjunction match?

jmattson · **Joined:** Wed Sep 15, 2004 8:11 pm **Posts:** 5

Criteria not supporting outer joins has left me completely stranded. I need to use Criteria and HQL is not an option for object retrieval and performace reasons. The fact that Criteria does not support outer joins has left me doing multiple quieries in code and joining them together manually. Can somebody please pay attention to this issue?!

clcantrell · **Joined:** Mon Oct 24, 2005 2:51 pm **Posts:** 7

I was able to create a criteria where outer joins on associations is possible. I create another createAlias(assoiciationPath, alias, FetchMode) on the Criteria interface and the CriteriaImpl. Had to modify the CriteriaJoinWalker. So far, seems to be working well. After unit testing, I intend to submit the changes.

pksiv · **Posted:** Wed Oct 26, 2005 9:38 am

jmattson wrote:

... HQL is not an option for object retrieval and performace reasons...

I don't understand this statement. What performance reasons ?

dnlazo · **Joined:** Mon Oct 31, 2005 6:24 pm **Posts:** 1

I have modified the Criteria Loader in Hibernate 3.0.5 to support Outer Joins in Criterias. If anyone else needes it you can ask. dnlazo@osde.com.ar

ponticello · **Posted:** Thu Apr 20, 2006 10:53 am

I'm wondering if anyone has a new solution to this problem, this thread is a few months old. I'm using Hibernate 3.1.3

I've got a branching domain model where a person is either a Employee or a non-employee and I want to search both groups at once.
Here's my Criteria expression - (although it's part of a very large search switch)

Code:

Criteria crit = session.createCriteria(Person.class);

crit.createCriteria("dutytours", "dt").createAlias("dutyassignments", "da");
crit.createCriteria("nfedutytours", "nfedt").createAlias("nfedutyassignments", "nfeda");
         
crit.add(
Expression.disjunction()
.add(Expression.conjunction()
        .add(Restrictions.isNull("da.enddate"))
   .add(Restrictions.in("da.assignmentdutystation",locationArray))
   )
.add(Expression.conjunction()
        .add(Restrictions.isNull("nfeda.enddate"))
   .add(Restrictions.in("nfeda.assignmentdutystation",locationArray))
   )
)

The query works fine except for the generated where clasue:

Code:

from PS.PERSON this_ 
left outer join PS.NONFEMAEMPLOYEE nonfemaemp6_ on this_.PERSONID=nonfemaemp6_.PERSONID
left outer join PS.EMPLOYEE employee7_ on this_.PERSONID=employee7_.PERSONID 
inner join PS.DUTYTOUR dt1_ on this_.PERSONID=dt1_.PERSONID 
inner join PS.DUTYASSIGNMENT da2_ on dt1_.REQUESTID=da2_.REQUESTID and dt1_.PERSONID=da2_.PERSONID 
inner join PS.NFEDUTYTOUR nfedt3_ on this_.PERSONID=nfedt3_.PERSONID 
inner join PS.NFEDUTYASSIGNMENT nfeda4_ on nfedt3_.NFEDUTYTOURID=nfeda4_.NFEDUTYTOURID 

left outer join ASD_MASTER.VW_ISAAC_PEOPLE vwisaacpeo12_ on this_.PERSONID=vwisaacpeo12_.USER_ID 

Any help would be greatly appreciated, I'm about to rewrite it in HQL.