JOIN table with itself using JPA Criteria API (or otherwise solve greatest-n-per-group)

Question

I'm trying to get a greatest-n-per-group query, and I would like to LEFT JOIN the table with itself, to obtain a query of the form:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

(taken from https://stackoverflow.com/a/7745635/1982385)

I would like to implement this using a JPA Criteria query (using Hibernate 5.2.4.Final), but the only way I can think of going about this is:

// query is a CriteriaQuery<>
Root<YourTable> queryRootTable = query.from(YourTable.class);
Join<YourTable, YourTable> selfJoin = queryRootTable.join(queryRootTable);

I've done several queries adding conditions to the .join() using .on(), but I am not sure how I would accomplish the .join() without a mapping (as in, without having a Hibernate-mapped field pointing to itself). Alternatively, another way of finding the greatest-n-per-group would be appreciated. (I am trying to find the row with the greatest rev, for each id, so a direct SELECT MAX(rev)... does not work).

Answer 1

I am sorry, if I did not understand your question completely and what do you mean by "without a mapping", my reputation does allow me to comment yet to clarify, but I guess solution is to do implicit join using where clause. (c is instance of CriteriaQuery, cb is instance of CriteriaBuilder)

 c.where(cb.equal(entity.get("id"), entity2.get("id");

Or let's we assume that :

• YourTable1 is mapped to Entity1.
• YourTable2 is mapped to Entity2.
• relationshipA is relationship between YourTable1 and YourTable2

Two comments :

First step, you need to define root of YourTable1

Root<Entity1> root = c.from(Entity1.class);

Second step, you can create join via relationship of YourTable1 and YourTable2, and specify join type.

Join<Entity1,Entity2> join = root.join("relationshipA", JoinType.LEFT);