HQL complex left joins and WITH clause

[Kanat Serikbayev]

Hello, everyone

I'm completely new to Hibernate and at the moment I'm stuck with a task to convert a native query to HQL.
I'm using Hibernate 4.1.6. The query contains a lot of left joins. I'm having a problem with a left join that has a reference to the previous left join.
This is how the native query looks like:

SELECT ORDERS.*, REQUEST.*, PERSON.LAST_NAME, ORDERS_STATUS_LN.NAME AS STATUS_NAME, RECEIPT.ID AS RECEIPT_ID, RECEIPT.SERIAL_NUMBER AS RECEIPT_SERIAL_NUMBER, RECEIPT_STATUS_LN.NAME AS RECEIPT_STATUS_NAME FROM REQUEST LEFT JOIN ORDERS ON ORDERS.ID = REQUEST.ORDERS_ID LEFT JOIN PERSON ON PERSON.ID = ORDERS.PERSON_ID LEFT JOIN ORDERS_STATUS_LN ON REQUEST.LAST_STATUS_ID = ORDERS_STATUS_LN.STATUS_ID LEFT JOIN RECEIPT ON RECEIPT.ID = REQUEST.RECEIPT_ID LEFT JOIN RECEIPT_STATUS_LN ON RECEIPT.LAST_STATUS_ID = RECEIPT_STATUS_LN.STATUS_ID AND RECEIPT_STATUS_LN.LOCALE_ID = ORDERS_STATUS_LN.LOCALE_ID LEFT JOIN LOCALE ON ORDERS_STATUS_LN.LOCALE_ID = LOCALE.ID WHERE LOCALE.LANGUAGE = ?

Specifically I'm having problems with this line
LEFT JOIN RECEIPT_STATUS_LN ON RECEIPT.LAST_STATUS_ID = RECEIPT_STATUS_LN.STATUS_ID AND RECEIPT_STATUS_LN.LOCALE_ID = ORDERS_STATUS_LN.LOCALE_ID

Hibernate provides WITH clause that can be used instead of ON. So I rewrote the query the following way:

Query query = session.createQuery("SELECT r, o, p.lastName, oSL.name, receipt.id, receipt.serialNumber, rSL.name "
                        + "FROM Request r "
                        + "LEFT JOIN r.orders o LEFT JOIN o.person p "
                        + "LEFT JOIN r.ordersStatus.ordersStatusLns oSL LEFT JOIN r.receipt receipt"
                        + "LEFT JOIN receipt.receiptStatus.receiptStatusLns rSL WITH rSL.locale = oSL.locale "
                        + "LEFT JOIN oSL.locale loc "
                        + "WHERE loc.language = :language ");

However, for some reason it seems to be not possible to reference the previous left join in this case (WITH rSL.locale = oSL.locale).
I get an exception stating
org.hibernate.hql.internal.ast.InvalidWithClauseException: with clause can only reference columns in the driving table

Am I doing something wrong? Or is that a limitation of HQL? If it's a limitation, is there a workaround to solve this problem?

[Bill Gorder]

There were some JIRA's with this functionality breaking in certain versions of hibernate. What are receiptStatus and receiptStatusLns?