Java QueryDSL Left Join 与附加条件 ON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28849713/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
QueryDSL Left Join with additional conditions in ON
提问by Cezille07
Is it possible to do the following query in QueryDSL?
是否可以在 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.
这使正确的 sql，但只加入零件号。
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).
检查生成的零件的库存可用性（除其他外）。左连接是必要的，因为我仍然需要没有库存条目的零件（例如新零件）。左连接将获得那些没有匹配库存余额的数据，但是month = MONTH(CURRENT_DATE)在查询的 where 子句中添加等等会删除没有库存余额的行（因为它们没有年/月数据）。
For the same reason @Whereand @Filterwould remove those parts from the resulting parts list and are not applicable. Sadly @Filterand @Whereare 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...)
出于同样的原因@Where，@Filter将从生成的零件列表中删除这些零件并且不适用。可悲的是@Filter，这@Where是我在 Google 和 SO 上进行搜索时获得的唯一其他结果。（奇怪的是，即使在会话中启用了过滤器，过滤器甚至不会影响查询......）
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?
最简单的解决方案是我最初的问题：如何将上述 SQL 转换为 QueryDSL？一般来说，是否可以向左连接的 ON 子句添加更多和/或自定义条件？这个问题的替代解决方案是什么？
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?
在查看文档后，似乎展示 querydsl 的旧博客具有's的on()功能leftJoin。为什么不再是这种情况？
SQLQuery(or HibernateSQLQueryor some other variety) has the on() function, but leftJoin() accepts RelationalPath<T>, not an EntityPath<T>as JPAQuerydoes. It seems impossible to cast QClasses to a RelationalPath, so that's probably not the way to go...
SQLQuery（或HibernateSQLQuery其他种类）具有 on() 函数，但 leftJoin() 接受RelationalPath<T>，而不是EntityPath<T>像JPAQuery那样。似乎不可能将 QClasses 转换为 a RelationalPath，所以这可能不是要走的路......
Update 2- We're using 2.9.0. Using on()gives an error, like it doesn't exist...
更新 2- 我们使用的是 2.9.0。使用on()会出现错误，就像它不存在一样......
采纳答案by vtor
It is possible to use on()in QueryDSL, including the latest version. JPAQueryalso supports on()predicate.
可以on()在 QueryDSL 中使用，包括最新版本。JPAQuery也支持on()谓词。
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);
JPAQueryimplements JPQLCommonQueryinterface, so as others it has all necessary methods.
JPAQuery实现JPQLCommonQuery接口，因此它具有所有必要的方法。
Here are docs from QueryDSLlatest version with left join using on()example.
以下是QueryDSL最新版本的文档，使用on()示例进行左连接。
Update:
更新：
on()has been introduced since QueryDsl 3.0.0 version. So for versions below 3.0.0 it is not available.
on()从 QueryDsl 3.0.0 版本开始引入。所以对于低于 3.0.0 的版本，它不可用。
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.
我建议至少将您的版本升级到 3.0.0，因为与旧版本相比，API 更强大。更重要的是，我强烈建议升级到最新的稳定版本（3.6.2），应该不会有任何问题，因为新 API 像以前一样支持所有内容，并具有附加功能。
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.
更新 2：正如评论中提到的@Cezille07 ，在旧版本中有一个with()替代方案on()。正如我们从issue 中看到的，with()已被替换为on()。
So for older versions with()does the trick. Here is a usefull linkwith more details.
所以对于旧版本来说with()，诀窍是。这是一个包含更多详细信息的有用链接。
