springspring-data-jpajpqljpa-criteria

Spring JPA Specification : How to sort on aggregate function using specification

Error is thrown when we are trying to sort on aggregated column COUNT.

For Example : Requirement is to form below query using specification. But, it is not returning single entity as result. Rather, it's join of tables.

Sort on SQL Query required using Specification :

Select DISTINCT ss.studentId.name, ss.studentId.department , COUNT (DISTINCT ss.subjectId.id)" +
            "FROM SubjectStudent  ss " +
            "left outer join ss.studentId sd  " +
            "left outer join ss.subjectId su "+
            "GROUP BY  ss.studentId.cve, ss.subjectId.id " +
            "ORDER BY ss.studentId.department

So, here how can we sort on aggregate column COUNT using specification?

Solution

  • Solution : criteriaBuilder.having(<>) whenever we want to put condition on aggregate functions using specification