JPA Query: join a subquery with grouping condition

Question

I have an entity that represents a change event for a specific object. Something like:

@Entity
public class Event {
    @Id
    private String eventId;
    private String objectId;
    private Instant creationDate;
    // other fields, getters, setters
}

There might be several event objects for a specific objectId.

Now I need to query all latest events for a each objectId (those that have max creationDate groping by objectId) .

If it was pure SQL I would write the following query:

SELECT event.*
FROM
  event event
  JOIN (
         SELECT
           e.object_id          object_id,
           MAX(e.creation_date) last_date
         FROM event e
         GROUP BY e.object_id
       ) latest_event
    ON latest_event.object_id = event.object_id
       AND event.creation_date = latest_event.last_date

But the similar join unfortunately doesn't work in JPA query.

Question: How to join a subquery in a JPA query?

Using a native query is not an option in my case, because I use Spring Data JPA repository with pagination functionality which doesn't work for native queries.

score 3 · Accepted Answer · answered Aug 17 '18 at 08:31

3

@Query( 
    value = "SELECT e FROM Event e " + 
            "WHERE e.creationDate = " +
            "(SELECT max(e2.creationDate) FROM Event e2 " + 
            "WHERE e2.objectId = e.objectId)"
)

answered Aug 17 '18 at 08:31

Morteza Jalambadani

1,881
5
19
30

This won't work. You ignore `objectId ` in the subquery which means that the max date you calculate has no relation to the object. – Sasha Shpota Aug 17 '18 at 08:33
@OleksandrShpota each row has a max date by own objectId select by this query – Morteza Jalambadani Aug 17 '18 at 08:35
Sorry, my fault. Somehow I thought that the subquery ends at the third line of the query. Let me check how it works. – Sasha Shpota Aug 17 '18 at 08:47
if max creation date are equal for 2 objectIds this won't work. – Rohit Aug 17 '18 at 08:49
@Rohit if we have two `objectId` and both has a Y `creationDate' and Y is `max creationDate` , wich select ? – Morteza Jalambadani Aug 17 '18 at 08:56
What if max creation date for objectId Y is equals to creation date of objectId Z which is not max creation date of objectId Z? – Rohit Aug 17 '18 at 09:11
@Rohit see forth line of query – Morteza Jalambadani Aug 17 '18 at 09:14

score -1 · Answer 2 · edited Oct 04 '18 at 15:13

-1

SELECT *
FROM  Event
NATURAL JOIN
    (SELECT object_id, MAX(creation_date) AS creation_date
    FROM Event
    GROUP BY object_id) groupedEvent

if two equal max creation_date be in the same object_id

SELECT ev.*
FROM Event ev
INNER JOIN 
      (SELECT max(id) AS id
       FROM Event e
       INNER JOIN 
              (SELECT object_id, MAX(creation_date) AS last_date
               FROM Event GROUP BY object_id
              ) groupedEvent 
       ON e.object_id = groupedEvent.object_id
       AND e.creation_date = groupedEvent.last_date
       GROUP BY e.object_id) dist 
ON ev.id = dist.id;

```

edited Oct 04 '18 at 15:13

onedaywhen

50,748
12
91
132

answered Aug 17 '18 at 09:18

yang_song

24
3

Why does your `NATURAL JOIN` have an `ON` clause ?? – onedaywhen Aug 17 '18 at 10:59
`NATURAL JOIN` is not supported in JPA query language. Apart from that your query is similar to what I've already tried - it doesn't work. – Sasha Shpota Aug 17 '18 at 11:04
@onedaywhen it's my fault , `NATURAL JOIN` no need `ON` condition – yang_song Sep 30 '18 at 01:44
@Oleksandr Shpota sorry to helpless,other one should be work, can you provide more desc for not work – yang_song Sep 30 '18 at 01:48
I don’t remember exact error, but it just didn’t compile the query. – Sasha Shpota Sep 30 '18 at 08:07

JPA Query: join a subquery with grouping condition

2 Answers2