5

I am trying to build this query with JPA Criteria API

SELECT s FROM snapshot s
INNER JOIN (
    SELECT collector_id, entity_id, MAX(timestamp) AS "timestamp"
    FROM snapshot GROUP BY collector_id, entity_id
) AS j ON s.TIMESTAMP = j.TIMESTAMP AND s.collector_id = j.collector_id AND s.entity_id = j.entity_id;

The inner select should get 3 properties to identify a snapshot, and then the outer select will get all the other properties of a snapshot based on 3 that inner select returned.

I have success with building the inner select, but how to combine the outer select with the inner using a join?

Or, maybe, there is a different way to construct the query itself in a way, that doesn't include a sub query...

EDIT:

Similar quertion: jpa criteria-api: join with subselect

snieguu
  • 2,073
  • 2
  • 20
  • 39
Nik
  • 133
  • 3
  • 9

1 Answers1

1

JPA does not support sub-selects in the FROM clause. Some JPA providers may support this.

For example EclipseLink does: http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Sub-selects_in_FROM_clause

Zielu
  • 8,312
  • 4
  • 28
  • 41
  • you can move it to the where claouse and use the subquery there. Luckily you are intereseted only in the max timestamp. – Zielu Mar 26 '15 at 14:04