CODEWITHSUNDEEP
playframeworkleft-joinjpql
Max
Max

Reputation: 643

How to do a complex LEFT JOIN condition in JPQL?

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

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

In an example I have, there are 2 tables:

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

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.

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

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

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

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.

Thanks

Upvotes: 2

Views: 5931

Answers (1)

JB Nizet
JB Nizet

Reputation: 691655

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

Upvotes: 9

Related Questions