CODEWITHSUNDEEP
javahibernatejpacriteria-api
Nik
Nik

Reputation: 143

JPA Criteria API: join on another query

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

Upvotes: 6

Views: 3419

Answers (1)

Zielu
Zielu

Reputation: 8552

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

Upvotes: 1

Related Questions