Hibernate Left Outer Join problem: path expected on join

Question

I have two tables, something like:
Article and ArticleRelevance
They have a one to one relationship, and ArticleRelevance is optional, meaning a Article may (or may not) have a ArticleRelevance.

I want to select all articles along with the ArticleRelevance details.

With traditional SQL, I will do a outer join on the tables, like

SELECT *
FROM ARTICLE A LEFT OUTER JOIN ARTICLE_RELEVANCE AR ON A.ARTICLE_ID = AR.ARTICLE_ID

I tried a similar query in HQL and got a Exception with message "Path Expected for Join"

I am unable to understand this message. What does this mean, and how do I solve it?

(As you probably have already guessed) I am an absolute hibernate newbie.

I have to use HQL. Actually I have an HQL already, joining two or three tables, and I have to add this condition.

StringBuffer hql = new StringBuffer();
hql.append(" select new service.orders.Order (order.orderNo, article.articleNo, article.articleName)");
hql.append(" from (Order order join  Article article with order.articleNo = article.articleNo) left outer join  ArticleRelevance articleRelevance with article.articleNo = articleRelevance.articleNo");
hql.append(" and order.orderNo =  "+orderNumber);

In this forum, someone says this is "missing functionality". True?

Answer 1

I tried a similar query in HQL and got a Exception with message "Path Expected for Join"

With HQL, a [ LEFT [OUTER] | INNER ] JOIN is done along an association path (constructed with an identification variable followed by a dot and an association field). So something like this in your case:

from Article a left join a.articleRelevance

The keyword OUTER is optional.

And your bigger query becomes:

select new service.orders.Order(order.orderNo, article.articleNo, article.articleName)
from Order order 
join order.article article
left outer join article.articleRelevance
where order.orderNo = :orderNo

Note the use of a named parameter :orderNo (don't concatenate parameters). Use it like this:

String queryString = "...";
Query q = session.createQuery(queryString);
q.setParameter("orderNo", orderNumber);
List<Order> results = q.list();

References

• Hibernate Core Reference Guide
  • 14.3. Associations and joins