根据实体中集合的大小排列结果集

Question

考虑以下实体-

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;
}

我需要订购团队结果集，以便团队具有最大的活跃&未过期的技能是第一位的。我正在使用弹簧靴规格&CriteriaQuery用于筛选不同字段中的团队。到目前为止，我有下面的代码，但没有按预期工作。

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 + "%"));
}
}

我这里缺了什么吗？请建议

Answer 1

要做到这一点，您必须首先连接表，然后过滤条目，将它们分组，然后对它们进行排序。

因此，您的SQL查询应该如下所示：

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

旁注：
在这种情况下不希望使用Specification，因为它们并不代表完整的查询，而是用于多个查询的语句或查询的一部分。

使用JPA标准查询：

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();
}

由于我个人觉得criteriaquery很难阅读，而且不直观，所以可以使用Querydsl作为替代方案。

使用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();
}