JPA Criteria Builder query with inner SELECT statement

Question

I need to create the below SQL join condition using JPA criteria builder,

SELECT * FROM student s1
INNER JOIN
(SELECT subject,teacher,MIN(marks) AS marks FROM student GROUP BY subject, teacher) s2
ON s1.subject = s2.subject
AND s1.teacher = s2.teacher
AND s1.marks = s2.marks

Below is the entity class and JPA query builder.

@Entity
@JsonIgnoreProperties(ignoreUnknown = true)
public class Student implements Serializable {
    
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="id")
    Long id;
    
    @Column(name="name")
    String name;
    
    @Column(name="subject")
    public
    String subject;
    
    @Column(name="teacher")
    String teacher;
    
    @Column(name="marks")
    String marks;
    
    @JsonIgnore
    @ManyToOne
    @JoinColumns({
    @JoinColumn(insertable=false, updatable=false, name="subject",referencedColumnName="subject"),
    @JoinColumn(insertable=false, updatable=false, name="teacher",referencedColumnName="teacher"),
    @JoinColumn(insertable=false, updatable=false, name="marks",referencedColumnName="marks")
    })
    Student studentSelf;
    
    @JsonIgnore
    @OneToMany(cascade = CascadeType.ALL, mappedBy="studentSelf")
    Set<Student> studentref;

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> query = cb.createQuery(Student.class);
Root<Student> mainStudent = query.from(Student.class);
List<Predicate> predicates = new ArrayList<>();

Join<Student, Student> studentJoin = mainStudent.join("studentSelf", JoinType.INNER);
        
List<Student> list = entityManager.createQuery(query).getResultList();

I am able to build the query with join condition but cannot create the inner SELECT query. How can i create a inner select query for join clause?

Requirement description: The below is the input table and output required.

One approach might be to rewrite the query using a correlated subquery in the `WHERE` clause to check for the minimum marks value. — Tim Biegeleisen, Jul 19 '20 at 09:02
But i am trying to extract min marks based on unique combination of Teacher and Subject. correlated subquery might come handy for this requirement. — VINAY S G, Jul 19 '20 at 09:23
I am trying to understand your model and the purpose of your query so it can be re-written in different way. And `studentSelf` is not a `@ManyToOne` relationship (i.e `subject`, `teacher` and `marks` cannot identify a student uniquely). If you want to keep `studentRef` (which represents `classmates` ) as uni-directional `@OneToMany` — Kavithakaran Kanapathippillai, Jul 19 '20 at 09:26
@KavithakaranKanapathippillai, The requirement is to extract the min marks for each combination of subject and teacher. I have added requirement in the query. — VINAY S G, Jul 19 '20 at 09:56

score 3 · Answer 1 · answered Jul 20 '20 at 12:04

Minor changes made to above answer,

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Student> query = cb.createQuery(Student.class);
        Root<Student> mainStudent = query.from(Student.class);

        Subquery<Student> subQuery = query.subquery(Student.class);
        Root subQueryRoot = subQuery.from(Student.class);

        Predicate predicate1 = cb.equal(mainStudent.get("teacher"), 
                                        subQueryRoot.get("teacher"));
        Predicate predicate2 = cb.equal(mainStudent.get("subject"), 
                                        subQueryRoot.get("subject"));
        Predicate finalPredicate = cb.and(predicate1, predicate2);

        subQuery.select(cb.min(subQueryRoot.get("marks"))).where(finalPredicate);

        query.select(mainStudent).where(mainStudent.get("marks").in(subQuery));
        return entityManager.createQuery(query).getResultList();

Kavithakaran Kanapathippillai · Accepted Answer · 2020-07-20T12:17:07.963

I have not tried this and it cloud fail miserably with an exception. But Can you give a try?

Remove the studentSelf and studentRef if you added them just for this query. @ManyToOneonstudentSelf` is not either as it will point to many records
I think the following query is equivalent to what you are trying to achieve.

    SELECT * 
    FROM student s1
    WHERE s1.marks 
    IN
    (
      SELECT MIN(s2.marks) 
      FROM student s2
      where s1.subject = s2.subject
      AND  s1.teacher = s2.teacher
    )

Then I am trying to translate it to CriteriaQuery

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Student> query = cb.createQuery(Student.class);
    Root<Student> mainStudent = query.from(Student.class);

    Subquery<Student> subQuery = query.subquery(Student.class);
    Root<Student> subQueryRoot = subQuery.from(Student.class);

    Predicate predicate1 = cb.equal(mainStudent.get("teacher"), 
                                    subQueryRoot.get("teacher"));
    Predicate predicate2 = cb.equal(mainStudent.get("subject"), 
                                    subQueryRoot.get("subject"));
    Predicate finalPredicate = cb.and(predicate1, predicate2);

    subQuery.select(cb.min(subQueryRoot.get("marks"))).where(finalPredicate);

    query.select(mainStudent).where(mainStudent.get("marks").in(subQuery));
    em.createQuery(issueQuery).getResultList();

Update

Updated subquery.correlate(mainStudent); to subQuery.from(Student.class); based on vinay-s-g comments

I haven't tried the solution. I will wait for feedback from OP, if it does not work, will set up a sample and experiment. — Kavithakaran Kanapathippillai, Jul 19 '20 at 11:56
Thank you @KavithakaranKanapathippillai, Your solution worked with some minor changes. But the question remains, can't we join a SELECT query using Hibernate. I am trying with an API EasyCriteria 2.0 but still not able to do it. — VINAY S G, Jul 20 '20 at 12:01
I will update the answer for reference. Regarding the second question, it is not possible `Joining unrelated entities is only possible when using JPQL or HQL. This feature is not available when using the JPA Criteria API` — Kavithakaran Kanapathippillai, Jul 20 '20 at 12:15

JPA Criteria Builder query with inner SELECT statement

2 Answers2