QueryDSL Left Join with additional conditions in ON

Question

Is it possible to do the following query in QueryDSL?

SELECT p.*
FROM parts_table p LEFT JOIN inventory_balance_table i ON 
    (p.part_no = i.part_no 
     AND i.month = MONTH(CURRENT_DATE) 
     AND i.year = YEAR(CURRENT_DATE));

Inventory balance stores inventory data for every part number/month/year; I need the only the data for the current year and month.

I've gotten the basic left join down:

QPartsTable qParts = QPartsTable.partsTable;
QInventoryBalance qBalance = QInventoryBalance.inventoryBalance;

JPAQuery q = new JPAQuery(em);
q.from(qParts).leftJoin(qParts.inventoryBalance, qBalance);
q.where(...);
List<Part> list = q.list(qParts);

which makes the correct sql, but only joining on the part number.

The resulting parts are checked for stock availability (among other things). The left join is necessary, because I still need parts that don't have an inventory entry yet (new parts for instance). Left join will get those without a matching inventory balance, but adding month = MONTH(CURRENT_DATE) and so on to where clause of the query removes the rows without an inventory balance (because they don't have year/month data).

For the same reason @Where and @Filter would remove those parts from the resulting parts list and are not applicable. Sadly @Filter and @Where are the only other results I'm getting with a search in Google and here on SO. (Oddly the Filter doesn't even affect the query even if filters are enabled in the session...)

The simplest solution would be my original question: How to turn the above SQL into QueryDSL? In general, is it possible to add more and/or custom conditions to the ON clause of the left join? What are the alternative solutions to this problem?

Thanks in advance!

---

Update - A follow-up question and an observation: (Perhaps this should be a new question entirely?)

After looking through the docs, it seems the older blogs demonstrating querydsl had the on() function for leftJoin's. Why is this no longer the case?

SQLQuery (or HibernateSQLQuery or some other variety) has the on() function, but leftJoin() accepts RelationalPath<T>, not an EntityPath<T> as JPAQuery does. It seems impossible to cast QClasses to a RelationalPath, so that's probably not the way to go...

---

Update 2 - We're using 2.9.0. Using on() gives an error, like it doesn't exist...

Solution

It is possible to use on() in QueryDSL, including the latest version. JPAQuery also supports on() predicate.

So this can be achieved,

QPartsTable qParts = QPartsTable.partsTable;
QInventoryBalance qBalance = QInventoryBalance.inventoryBalance;

JPAQuery q = new JPAQuery(em);
q.from(qParts).leftJoin(qParts.inventoryBalance, qBalance).on(qBalance.month.eq(yourMonth).and(qBalance.year.eq(yourYear))).list(qParts);

JPAQuery implements JPQLCommonQuery interface, so as others it has all necessary methods.

Here are docs from QueryDSL latest version with left join using on() example.

Update:

on() has been introduced since QueryDsl 3.0.0 version. So for versions below 3.0.0 it is not available.

I'd suggest to upgrade your version at least to 3.0.0, as the API is quite stronger comparing to old versions. Even more, I'd strongly advice to upgrade to the latest stable version (3.6.2), there shouldn't be any problems as new API supports everything as before, with additional features.

Update 2: As @Cezille07 mentioned in the comment, there is a with() alternative for on(), in older versions. As we see from the issue , with() has been replaced with on() later on.

So for older versions with() does the trick. Here is a usefull link with more details.