Thursday, October 25, 2007

NHibernate Non-Mapped Joins

Recently I received a question asking how to perform an HQL query which joined two entities which were not related in the mappings. I realized that this isn't a simple straight forward concept like most new NHibernate users would probably expect. It doesn't work the way I think 95% of users would initially try to accomplish it.

Some times we need such functionality because we are concerned of our collections growing too large if all collections are mapped. Lets say that for whatever reason we have a Customer class which is referenced by an Order class but the Customer does not have a collection of Orders. Lets say we also track wish list items to show items the customer would like to receive, but again do not have a collection of WishListItem objects on the Customer class.

Now we may think we would write a HQL query to show orders which were placed for an item in the customer's wish list like the following:


SELECT o
FROM Order o
INNER JOIN o.OrderLine ol
INNER JOIN WishListItem wli
ON (wli.Customer = o.Customer AND wli.Product = ol.Product)
WHERE o.Customer = :customer


Unfortunately we would be wrong. HQL is not SQL and does not follow the ANSI SQL syntax we have become so familiar with. Even though INNER JOIN is a keyword in HQL it does not work like it does in SQL. Instead a non-mapped join is represented much like the old style SQL joins. We would instead use the following HQL query:


SELECT o
FROM Order o
INNER JOIN o.OrderLine ol, WishListItem wli
WHERE o.Customer = :customer
AND wli.Product = ol.Product
AND o.Customer = wli.Customer


I hope this helps anyone struggling to create HQL queries between entities which are not explicitly mapped.

--John Chapman

7 comments:

Eliana said...

Dear John,
I am new in VB.NET but have worked with Hibernate. Now I am interested in integrating NHibernate with VB.NET but cannot find any examples. My apologies if this is not the proper place to post this, but will appreciate any guidance. Thank you.
Eliana

John Chapman said...

Eliana, go to the NHibernate web site (www.nhibernate.org) They have links to a forum with a lot of good information, as well as documentation which includes a quick start guide that should get you up and going. You can find the documentation here:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html_single/

Anonymous said...

can you plz put a full example of non mapped joins it will be much easier then plz use three tables for inner join and also a example with two tables for inner join thanks

Manitra said...

Hi,
Thanks for this example.

I was wondering you have a similar trick for OUTER JOIN. (your example works exactly like an INNER JOIN)

sysmat said...

If I try your's HQL I get error:

java.lang.IllegalArgumentException: org.hibernate.QueryException: outer or full join must be followed by path expression

using hib 3.5.1-Final

DeEpAk said...

"select max(mt.ReportingPeriodID) from MonthlyTranche mt INNER JOIN InvestmentLoan il , QuarterlyInvestmentDetails qid where mt.LoanNameID = il.LoanNameID and il.InvestmentID = qid.InvestmentID and qid.ReportingManagementRegionID = " + _managementRegionID
+ " and qid.ActiveIndex = 1 and il.IsActive = 1 and mt.IsActive = 1"

and i got this error : outer or full join must be followed by path expression

Sipke Schoorstra said...

This is perfect, just what I was looking for. Thanks for sharing this.

Blogger Syntax Highliter