order entity based on size of collection

[Tapan Agarwal]

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 on 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 order = cb.desc(cb.size(root.join("employees").get("skills")));
    cq.orderBy(order));

    return cb.like(cb.equal(root.get("name"), "%" + value + "%"));
  }
}

Anything I am missing here? please suggest

[Himai Minh]

Maybe, try this:

Root<Team> team = cq.from(Team.class);
Join<Team, Employee> employee = team.join(Team_.employees);

[Tapan Agarwal]

Himai Minh wrote:Maybe, try this:

Root<Team> team = cq.from(Team.class);
Join<Team, Employee> employee = team.join(Team_.employees);

Hi Himai, the code will only perform the join between Team and Employee, which I have already done in my code. I am unable to pull the total number of the skills of each team.

[Himai Minh]

How about building a query like select count(distinct skills) from team_employee where team_employee is the joint table?
I am not  familiar with Specification though.

[Tapan Agarwal]

Himai Minh wrote:How about building a query like select count(distinct skills) from team_employee where team_employee is the joint table?
I am not  familiar with Specification though.

I am stuck with Specification here since this is on an old code where I don't really have an option to change a lot of things.

[Himai Minh]

In your toPredicate method, is it possible to use the criteria builder to build
a query like this one "select count( distinct skills) from Team_Employee "  where Team_Employee is the joint table ?