如何在JPQL中执行复杂的LEFT JOIN条件? [英] How to do a complex LEFT JOIN condition in JPQL?

问题描述

我正在使用JPQL进行Play框架中的模型查询.

I am using JPQL for the model queries from Play Framework.

我想知道JPQL是否支持LEFT JOIN的复杂" ON条件.

I wonder if JPQL supports "complex" ON condition for LEFT JOIN.

在我的示例中，有2个表:

In an example I have, there are 2 tables:

• 应用程序"-应用程序列表
• 'AggregationHistory'-每个日期每个应用程序的聚合记录列表.在模型中，它具有"app"字段，代表 与"App"(物理表中的列名"app_id")一对多

• 'App' - list of applications
• 'AggregationHistory' - the list of the aggregation records per application, per date. In the model, it has 'app' field representing many-to-one with 'App' (column name 'app_id' in the physical table)

假设我要计算没有具有特定日期记录的所有应用程序的列表.

Suppose I want to calculate the list of all the apps that do not have a record for a specific date.

在普通SQL中，我使用以下查询获取结果:

In plain SQL, I get the result using the following query:

SELECT a.* FROM app a LEFT JOIN aggregationhistory ah 
ON a.id = ah.app_id AND ah.fromDate =  '2012-03-14 00:00:00' 
WHERE ah.fromDate is NULL

处于开"状态的与"是必不可少的.

'AND' in 'ON' condition is essential, of course.

现在，我看到的所有JPQL示例都如下:

Now, all the examples of JPQL I see are like:

SELECT a.* FROM AggregationHistory ah  LEFT JOIN ah.app a WHERE ...

那么，唯一支持的"ON"条件是ID的匹配"吗? (似乎对我没有多大帮助)

So, the only 'ON' condition supported - is the "match" of the IDs? (WHERE seems not to help me much)

我可以考虑解决方法(例如，使用本地查询"，或使用JOIN获取确实有记录的应用程序列表并进行比较).但是我不知道我在SQL中进行的查询-是否可以转换为JPQL.

I can think of workarounds (like, using a "native query", or using JOIN to get the list of the applications that do have a record, and compare). But I wonder if the query I made in SQL - can be converted into JPQL.

谢谢

推荐答案

JPIFL，AFAIK，不支持ON子句，但HQL确实支持它们.不过，选择使用with关键字:

JPQL, AFAIK, doesn't support ON clauses, but HQL does support them. The chose to use the with keyword though:

select a from App a 
left join a.history h with h.fromDate = :fromDate
where h.fromDate is null

这篇关于如何在JPQL中执行复杂的LEFT JOIN条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！