I am trying to create a filter query using JPA Criteria API that finds all those entities that have a lowest value in their group.
For example given a JPA entity "Student" mapping a Table similar to:
| id | name | grade | class |
|---|---|---|---|
| 1 | John | 1 | A |
| 2 | Jim | 2 | A |
| 3 | James | 4 | B |
| 4 | Jordan | 3 | B |
I would like to filter this down to the students that have the lowest grade in their class, in this case John and Jordan.
In SQL this can be accomplished with a self join like this:
SELECT *
FROM students s1
inner join(
select class, Min(grade) as max_grade from students group by class
) s2 on s1.class = s2.class and s1.grade = s2.max_grade;
However I am unable to find a way to create a similar self join approach using the JPA Criteria API.
I can not just use JPQL for this as this predicate would have to be added to a large, existing Criteria Predicate.