JPA CriteriaBuilder - sort by the number of associated entities in a one-to-many relationship
Solution 1
Supposing that the entity Customer has a OneToMany
property like this:
@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_.orders
with "orders"
and Customer_.id
with "id"
.
If the OneToMany
property is of another type, replace CollectionJoin
with the collection of the proper type (ListJoin
, SetJoin
, MapJoin
).
Solution 2
Use this inside the specification
cq.orderBy(cb.desc(cb.count(orders)));
Also send PageRequest(1, 10, Sort.unsorted())
. This is how I did it.
If you are passing the Sort value as unsorted and then override criteria query with your own logic of sorting on your joined entity
Alex
Updated on July 14, 2022Comments
-
Alex almost 2 years
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:
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.
Any help in composing the query using CriteraBuilder would be much appreciated.
Thank you in advance.