JPA CriteriaBuilder - sort by number of related objects in a one-to-many relationship
I have two Customer and Order objects in a one-to-many relationship. For each customer, I need to count the number of related orders and sort the results by that number. In the original 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 have to do it with the CriteriaBuilder because this request is part of a larger piece of code that uses the CriteriaBuilder to set additional conditions. In Hibernate, I would probably use Projections, but I can't find anything like this in JPA.
Any help with composing your query using CriteraBuilder would be much appreciated.
Thanks in advance.
source to share
Suppose the Customer object has a property OneToMany
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 property OneToMany
is another type, change CollectionJoin
the selection of the correct type ( ListJoin
, SetJoin
, MapJoin
).
source to share