QueryDSL左联接，附加条件为ON [英] QueryDSL Left Join with additional conditions in ON

问题描述

是否可以在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));

库存余额存储每个零件号/月/年的库存数据；我只需要当前年份和月份的数据.

我已经基本离开了:

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

这将生成正确的sql，但仅加入零件号.

检查生成的零件的库存可用性(以及其他).左连接是必要的，因为我仍然需要没有库存条目的零件(例如，新零件).左联接将得到那些没有匹配的库存余额的行，但是在查询的where子句中添加month = MONTH(CURRENT_DATE)等，将删除没有库存余额的行(因为它们没有年/月数据).

出于相同的原因，@Where和@Filter将从结果零件列表中删除那些零件，因此不适用.可悲的是，@Filter和@Where是我在Google以及此处的搜索中获得的仅有的其他结果. (奇怪的是，即使在会话中启用了过滤器，过滤器甚至都不会影响查询.)

最简单的解决方案是我最初的问题:如何将上述SQL转换为QueryDSL?通常，是否可以向左联接的ON子句添加更多和/或自定义条件?有什么替代方法可以解决此问题?

提前谢谢！

---

更新-后续问题和观察结果:(也许这应该完全是一个新问题?)

浏览文档后，似乎老式的博客表明querydsl具有leftJoin的on()功能.为什么不再是这种情况?

SQLQuery(或HibernateSQLQuery或其他某种形式)具有on()函数，但leftJoin()接受RelationalPath<T>，而不是JPAQuery接受的EntityPath<T>.将QClasses强制转换为RelationalPath似乎是不可能的，所以这可能不是可行的方法.

---

更新2 -我们正在使用2.9.0.使用on()会给出错误，就像它不存在...

解决方案

可以在QueryDSL中使用on()，包括最新版本. JPAQuery也支持on()谓词.

所以这可以实现，

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实现了JPQLCommonQuery接口，因此它具有所有必需的方法.

以下是来自 QueryDSL 使用on()示例的左连接的最新版本.

更新:

从QueryDsl 3.0.0版本开始引入

on().因此，对于低于3.0.0的版本，此功能不可用.

我建议您至少将您的版本升级到3.0.0，因为与旧版本相比，该API更加强大.甚至，我强烈建议您升级到最新的稳定版本(3.6.2)，应该不会有任何问题，因为新的API支持以前的所有功能以及其他功能.

更新2: 如评论中提到的@ Cezille07，在较旧的版本中，有一个with()替代品on().从问题中可以看到，with()稍后将被替换为on(). .

因此对于较旧的版本，with()可以解决问题.这是有用的链接，其中包含更多详细信息.

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...

解决方案

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.

这篇关于QueryDSL左联接，附加条件为ON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！