Left JOIN in *subquery*

DarthMambo · **Joined:** Mon Feb 19, 2007 5:10 am **Posts:** 15

I've been trying to write a subquery using the DetachedCriteria objects and want to make my subquery use a LEFT JOIN:

Code:

DetachedCriteria myDetatchedCriteria = DetachedCriteria.For(typeof(Contact), "contact");
myDetatchedCriteria.CreateAlias("Group", "g", JoinType.LeftOuterJoin);

However, this overloaded version of CreateAlias(...) which takes a JointType doesn't appear to be available to the DetatchedCriteria object. Could someone please confirm whether or not a LEFT JOIN in a subquery is possible?

The code I'm writing at the moment is for an "Advanced Search" screen, so I don't really have the option of using HQL directly.

lowecg2004 · **Joined:** Fri Sep 15, 2006 10:18 am **Posts:** 8

Darth,

I'm having the same problem. Did you find a solution in the end?

Can anyone confirm whether or not you can do LEFT JOINs in subqueries?

Cheers,

Chris.

DarthMambo · **Joined:** Mon Feb 19, 2007 5:10 am **Posts:** 15

Still no solution on this. I can't find anything in the documentation which describes whether a LEFT JOIN in a subquery is possible.

Could someone please confirm?

sergey · **Posted:** Fri Mar 16, 2007 5:26 am

I believe it's possible, there's just no API to do it. Please submit a JIRA issue about this.

DarthMambo · **Joined:** Mon Feb 19, 2007 5:10 am **Posts:** 15

Thanks for your reply. I have raised a case: NH-944.

I have also included in the case a description of my workaround.

If anyone wants to see this feature added to Hibernate please vote for it.

lowecg2004 · **Joined:** Fri Sep 15, 2006 10:18 am **Posts:** 8

Okay - just added my vote to this.

Sergey, this feature would be *really* useful to me. I know you guys have enough on your plates already, but is there any chance this could make it into NHibernate within the 1.2.0 timeframe?

Best Regards,

Chris.

adamb451 · **Joined:** Thu Jan 26, 2006 4:27 am **Posts:** 2

I too would like the option to select the join type when creating detached criteria. I used detached criteria for generating dynamic searches -- without this feature I'm having trouble with ordering.

A drawing may have 0-1 vendors.

First Scenario

DetachedCriteria.For(typeof(Drawing), "drawing")
.AddOrder(Order.Asc("Name"));

Produces SQL akin to...

SELECT drawing.* FROM Drawing AS drawing ORDER BY drawing.Name

Second Scenario

DetachedCritiera criteria = DetachedCriteria.For(typeof(Drawing), "drawing");
critiera.CreateCriteria(typeof(Vendor), "vendor");
criteria.AddOrder(Order.Asc("vendor.Name"));

Produces SQL akin to...

SELECT drawing.* FROM Drawing AS drawing INNER JOIN Vendor as vendor ON drawing.Vendor = vendor.Uid ORDER BY vendor.Name

Since drawings have 0-1 vendors, the second scenario actually produces a different number of rows because of the inner join. The user still wants to see all drawings, just sort on vendor name. By specifying an outer join in the DetachedCriteria.CreateCriteria method (like you can with ICriteria.CreateCriteria) this could be achieved simply.

cordelia · **Joined:** Fri Oct 05, 2007 3:05 pm **Posts:** 2

Chris,

I hope this will be helpful : http://www.flux88.com/CategoryView,cate ... rnate.aspx .

Cheers.

bsdwork · **Joined:** Thu Sep 27, 2007 10:48 am **Posts:** 5

Cordelia,

Indeed there were useful information written there, but.. still no luck for me.I prefer the topics about plavix generic or anything related to chemistry/biology! :D