java JPA CriteriaBuilder - 按一对多关系中关联实体的数量排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14425674/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPA CriteriaBuilder - sort by the number of associated entities in a one-to-many relationship
提问by Alex
I have two entities Customer and Order in a one-to-many relation. For each customer I need to count the number of associated orders and sort the results by this number. In a native postgres query it looks like this:
我有两个实体 Customer 和 Order 处于一对多关系。对于每个客户，我需要计算关联订单的数量并按此数字对结果进行排序。在原生 postgres 查询中，它看起来像这样：
select cust.id, count(order.id) from customers cust
left outer join orders order
on cust.id = order.customer_id
where .... conditions ...
group by cust.id
order by count desc;
But I must do this using CriteriaBuilder because this query is part of a larger piece of code that uses CriteriaBuilder to put in additional conditions. In Hibernate I would have probably used Projections, but I can't find anything similar in JPA.
但我必须使用 CriteriaBuilder 来执行此操作，因为此查询是使用 CriteriaBuilder 放入附加条件的较大代码段的一部分。在 Hibernate 中，我可能会使用 Projections，但在 JPA 中找不到类似的东西。
Any help in composing the query using CriteraBuilder would be much appreciated.
使用 CriteraBuilder 编写查询的任何帮助将不胜感激。
Thank you in advance.
先感谢您。
回答by perissf
Supposing that the entity Customer has a OneToManyproperty like this:
假设实体 Customer 有这样的OneToMany属性：
@OneToMany(mappedBy = "customerId")
private Collection<Order> orders;
You can use the following query:
您可以使用以下查询：
EntityManager em; // to be built or injected
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Customer> customer = cq.from(Customer.class);
CollectionJoin<Customer, Order> orders = customer.join(Customer_.orders, JoinType.LEFT);
cq.select(cb.tuple(customer, cb.count(orders)));
cq.where(... add some predicates here ...);
cq.groupBy(customer.get(Customer_.id));
cq.orderBy(cb.desc(cb.count(orders)));
List<Tuple> result = em.createQuery(cq).getResultList();
for (Tuple t : result) {
Customer c = (Customer) t.get(0);
Long cnt = (Long) t.get(1);
System.out.println("Customer " + c.getName() + " has " + cnt + " orders");
}
The above approach uses Metamodel. If you don't like it, you can replace Customer_.orderswith "orders"and Customer_.idwith "id".
上述方法使用Metamodel。如果你不喜欢它，你可以替换Customer_.orders使用"orders"，并Customer_.id用"id"。
If the OneToManyproperty is of another type, replace CollectionJoinwith the collection of the proper type (ListJoin, SetJoin, MapJoin).
如果该OneToMany属性是另一种类型，则替换CollectionJoin为正确类型 ( ListJoin, SetJoin, MapJoin)的集合。
