Java – additional conditions for adding querydsl to on

Can I execute the following queries 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));

The inventory balance stores the inventory data of each part number / month / year; I only need the data of the current year and month

I've got the basic left connection:

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);

This makes the correct SQL, but only the part number is added

Check the stock availability (among other things) of the generated parts The left connection is necessary because I still need parts without inventory items (e.g. new parts) The left join will get those without matching inventory balance, but adding month = month (current_date) and so on will delete the rows without inventory balance in the where clause of the query (because they have no year / month data)

For the same reason, @ where and @ filter remove these parts from the resulting parts list, but not applicable Sadly, @ filter and @ where are the only other results I get in Google and so searches (strangely, even if the filter is enabled in the session, the filter will not affect the query...)

The simplest solution is my original question: how to convert the above SQL into query DSL? In general, can you add more and / or custom conditions to the on clause of the left join? What is the alternative solution to this problem?

Thank you in advance!

Update – follow up questions and observations: (maybe this should be a completely new question?)

After reviewing the documentation, it seems that older blogs have proved that querydsl has the on () function of leftjoin Why is this no longer the case?

SqlQuery (or hibernatesqlquery or some other type) has an on () function, but leftjoin () accepts relationalpath < T > instead of entitypath < T > Just like JP aquery It seems impossible to convert qclasses to relationalpath, so this may not be that way

Update 2 – we are using 2.9 0. Using on() will generate errors as if it didn't exist

Solution

You can use on () in query DSL, including the latest version Jpaquery also supports the 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 the jpqlcommonquery interface, so others have all the necessary methods

The following is the document of the latest version of querydsl, in which the left connection uses the on () example

to update:

From querydsl 3.0 Version 0 began to introduce on() Therefore, for less than 3.0 Version of 0, which is not available

I recommend upgrading your version to at least 3.0 0, because the API is much more powerful than the old version More importantly, I strongly recommend upgrading to the latest stable version (3.6.2). There should be no problems, because the new API supports all content and has other functions

Update 2: as mentioned in the comment @ cezille07, on () replaces on () in the old version As we can see from the issue, with () has been replaced later with on ()

So it's the trick for older versions with () This is a useful link with more details