Consider the entities below -
class Team {
private String name;
@OneToMany
private Set<Employee> employees;
}
class Employee {
private String name;
@OneToMany
private Set<Skill> skills;
}
class Skill {
private String name;
private boolean active;
private Date expiryDate;
}
I need to order the Teams resultset such that the team with maximum active & unexpired skills comes first. I am using spring boot Specification & CriteriaQuery to filter Teams in different fields. So far I have the code below which doesn't work as expected.
public class TeamSpecs implements Specification<Team> {
@Override
public Predicate toPredicate(Root<Team> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
Order o = cb.desc(cb.sum(cb.size(root.join("employees").get("skills")));
cq.orderBy(o));
return cb.like(cb.equal(root.get("name"), "%" + value + "%"));
}
}
Anything I am missing here? please suggest
For this to work you first have to join your tables, then filter the entries, group them together and then sort them.
So your SQL query should look like this:
Sidenote:
Using a
Specification
in this case is not what you want to do, because they do not represent a complete Query, but a statement or part of a query, that is used in multiple queries.Using JPA criteriaquery:
Since I personally find criteriaquery hard to read and unintuitive you could use Querydsl as an alternative.
Using Querydsl: