CODEWITHSUNDEEP
springdatabasejpajpql
mika91
mika91

Reputation: 53

Need help to write a query in JPQL, without subqueries

I need to execute a simple query on a single database, but because of the lack of subquery in JPQL (except HAVING and EXITS clauses), I don't known how to write it.

The table looks like this

id  |  eventType | occurenceDate   | comment | data | ...
-----------------------------------------------------------
1   |  event-A   | 2020-09-14      | ...
2   |  event-B   | 2020-09-09      | ...
3   |  event-A   | 2020-09-13      | ...
4   |  event-A   | 2020-09-10      | ...
5   |  event-B   | 2020-09-20      | ...
6   |  event-C   | 2020-09-11      | ...

I need a query to get all next event occurence by type, at given reference date. For example, if the reference date is '2020-09-12', I want the query to return entity with id=3 and 5

No problem to get a list of eventType/occurenceDate tuples (which is a unique composite key)

SELECT t.type, min(t.occurenceDate) 
FROM table t
WHERE t.occurenceDate > :referenceDate
GROUP BY t.eventType

but without subqueries, I don't know how to get the full entities.

Any help ? Thanks

Upvotes: 0

Views: 220

Answers (2)

mika91
mika91

Reputation: 53

ok. Just change your suggested query a little, and it's working! Didn't know it was possible (or think) to inject t1 record in the subquery. (yes my DB knowledge are small ^^)

SELECT t1
FROM table t1
WHERE t1.occurrenceDate = (
    SELECT MIN(t2.occurrenceDate)
    FROM table t2
    WHERE t2.eventType = t1.eventType 
    AND t2.occurenceDate > :referenceDate
);

Thank you very much

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Actually, subqueries are the way you would typically handle this query in JPQL:

SELECT t1
FROM table t1
WHERE t.occurenceDate > :referenceDate AND
      t.occurrenceDate = (SELECT MIN(t2.occurrenceDate)
                          FROM table t2
                          WHERE t2.eventType = t1.eventType);

On most databases, there is really no way to get the full records without a formal subquery (SQL Server might be one exception, but even there you would still have to use a native non JPQL query).

Upvotes: 1

Related Questions