Order result set based on the size of collection in an entity

Question

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

Answer 1

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:

select team.*
from Team team
  inner join employee
  inner join skill
where skill.active = true and skill.expiryDate > today
group by team.name
order by count(skill.name) desc

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:

    public List<Team> getTeamsWithActiveSkills() {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Team> cq = cb.createQuery(Team.class);
        Root<Team> root = cq.from(Team.class);
        Join<Team, Employee> employees = root.join("employees");
        Join<Team, Skill> skills = employees.join("skills");

        Predicate isActive = cb.isTrue(skills.get("active"));
        Predicate isNonExpired = cb.greaterThan(skills.get("expiryDate"), LocalDate.now());

        cq.where(isActive, isNonExpired).groupBy(root.get("name"));

        Order order = cb.desc(cb.count(skills));
        cq.orderBy(order);

        return em.createQuery(cq).getResultList();
    }

Since I personally find criteriaquery hard to read and unintuitive you could use Querydsl as an alternative.

Using Querydsl:

public List<Team> getTeamsWithActiveSkills() {

        QTeam team = QTeam.team;
        QEmployee employee = QEmployee.employee;
        QSkill skill = QSkill.skill;
        JPQLQuery<Team> query = from(team).join(team.employees, employee).join(employee.skills, skill);

        query = teamJPQLQuery.where(skill.active.isTrue().and(skill.expiryDate.gt(LocalDate.now())));

        query = query .groupBy(team.name);
        query = query .orderBy(skill.name.count().desc());

        return query.fetch();
    }