您无法为实体编写此类查询。如果您使用本机 SQL,您可以通过 user.id 按组织名称进行 GROUP BY,但这不是您想要的。
您可以简单地获取加入用户和组织:
final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
final CriteriaQuery<User> criteria = builder.createQuery(User.class);
Root<User> root = criteria.from(User.class);
Fetch<User, Organization> organizations = root.fetch("organizations");
criteria.select(c);
TypedQuery<User> query = em.createQuery(criteria);
List<User> users = query.getResultList();
并在内存中对它们进行排序:
Collections.sort(users, new Comparator<User>() {
@Override
public int compare(User user1, User user2) {
SortedSet<Organization> organizations1 = user1.getOrganizations();
SortedSet<Organization> organizations2 = user2.getOrganizations();
if(organizations1.isEmpty()) {
if(organizations2.isEmpty()) {
return 0;
} else {
return -1;
}
} else {
if(organizations2.isEmpty()) {
return 1;
} else {
Organization o1 = organizations1.first();
Organization o2 = organizations2.first();
return o1.compareTo(o2);
}
}
}
});
Update
如果您不想获取内存中的所有内容进行排序,那么我认为仅使用 JPQL 或 Criteria API 是不可能的。
假设组织名称是 VARCHAR(50),我们需要首先使用右填充,然后我们需要运行group_concat
首先原生查询:
PostgreSQL
SELECT o.user_id,
string_agg(RPAD(o.name, 50) ORDER BY o.name) as ordr
FROM Organization o
GROUP BY o.user_id
order by ordr
MySQL
SELECT o.user_id,
group_concat(RPAD(o.name, 50) ORDER BY o.name) as ordr
FROM Organization o
GROUP BY o.user_id
order by ordr
对 user_id 进行排序后,您可以简单地将 user_ids 提取到List<Long>
然后运行第二个 JPQL 查询来获取用户:
List<Long> userIds = ...;
TypedQuery<User> q = em.createQuery(
"select u " +
"from User u " +
"where u.id in :userIds "
, User.class);
q.setParameter("userIds", userIds);
List<User> users = q.getResultList();