使用JPA规范按@oneToMany字段订购

Question

考虑下面的实体-

@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
@OneToMany(mappedBy = "employee", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private List<Phone> phones; //contains both "active" & "inactive" phones
}

@Entity
public class Phone {
@Id
@GeneratedValue
private long id;
private boolean active;
private String number;

@ManyToOne(fetch = FetchType.LAZY)
private Employee employee;
}

我需要拉所有的员工，并根据"活动的">他们有手机。请注意，员工可以有活动电话和非活动电话。所以我试图实现的查询是

ORDER BY (SELECT 
COUNT(phone4_.employee_id)
FROM
phone phone4_
WHERE
employee4_.id = phone4_.employee_id 
AND phone4_.active = true
) DESC

由于某种原因，我一直坚持这里的规范，下面是我使用的代码-

List<Order> orders = new ArrayList<>();
orders.add(cb.desc(cb.size(employee.get("phones"))));
cq.orderBy(orders);

当我运行代码时，生成的查询是

ORDER BY (SELECT 
COUNT(phone4_.employee_id)
FROM
phone phone4_
WHERE
employee4_.id = phone4_.employee_id) DESC

我无法将额外的AND条件添加到逻辑中。请建议

Answer 1

如Persistence API规范中所指定：

4.6.16子查询子查询可以在WHERE或HAVING子句中使用。

JPA不支持order by子句和select子句中的子查询。

不过，Hibernate ORM在SELECT和WHERE子句中支持它们。

因此，您不能编写该查询，也不能遵守JPA。

这个HQL应该可以工作，它由Hibernate ORM:覆盖

SELECT e1, (SELECT count(p)
FROM Phone p
WHERE p.active = true AND p.employee = e1) as activeCount
FROM Employee e1
ORDER BY activeCount DESC

令人惊讶的是，用条件编写这个查询不起作用：

CriteriaBuilder builder = ormSession.getCriteriaBuilder();
CriteriaQuery<Object> criteria = builder.createQuery();
Root<Employee> root = criteria.from( Employee.class );
Subquery<Long> activePhonesQuery = criteria.subquery( Long.class );
Root<Phone> phoneRoot = activePhonesQuery.from( Phone.class );
Subquery<Long> phonesCount = activePhonesQuery
.select( builder.count( phoneRoot ) )
.where( builder.and( builder.isTrue( phoneRoot.get( "active" ) ), builder.equal( phoneRoot.get( "employee" ), root ) ) );
criteria.multiselect( root, phonesCount )
.orderBy( builder.desc( phonesCount ) );

原因是，Hibernate ORM试图在order by子句中扩展子查询，而不是引用别名。正如我之前提到的，这是不受支持的。

如果您不想使用本机查询，我认为HQL是最简单的选择。