2

I'm trying to get a 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).

Daniel Gray
  • 1,697
  • 1
  • 21
  • 41
  • 1
    Have you tried joining the two instances of `YourTable` ... without using join but simply with a `where` condition? – perissf Dec 21 '16 at 16:31
  • i.e "SELECT a FROM YourEntity a, YourEntity b WHERE ..." – Neil Stockton Dec 21 '16 at 18:17
  • I have tried a cross join like you have said, putting the condition in the WHERE, but it does not work (it seems that it depends on the way the LEFT JOIN works to get the solution to the problem). – Daniel Gray Dec 22 '16 at 07:52

1 Answers1

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);

fg78nc
  • 4,774
  • 3
  • 19
  • 32
  • The problem I have is that there is only *one* entity (`YourTable`) which, therefore, has no "mapping" relationship to itself for me to do the `.join()` on. In any case, thanks for the answer! (and you should now be able to comment) – Daniel Gray Dec 22 '16 at 07:38
  • I see, well you can self-reference (relate to itsellf), and do `join `, or you can join with `while ` even if you have only one entity, by comparing it's attributes. Yes, I can comment now, thanks. – fg78nc Dec 22 '16 at 13:31
  • I ended up using a native SQL query from Hibernate. Unfortunately, directly cross joining with "where" for comparing the fields did not work, as the query I needed relies on the behavior of the LEFT JOIN. – Daniel Gray Dec 22 '16 at 14:01