How to filter a collection in JPA / JPQL?

Question

I have two objects:

@Entity public class Customer implements java.io.Serializable { ... @OneToMany(fetch=FetchType.EAGER, mappedBy="customer") private Set<CustomerOrder> customerOrders; ... @Entity public class CustomerOrder implements java.io.Serializable { .... private double cost; @ManyToOne @JoinColumn(name="CUST_ID") public Customer customer; ... 

Now in my JPQL I want to return these customers with CustomerOrder.cost> 1000. For example, there are three clients A, B and C. A has two orders with a value of = 1000 and 2000, respectively. B has three orders with cost = 2000-3000 and 500, respectively. C has one order with cost = 500. Now I want to get three customers: A returns orders only with cost = 2000; B returns orders from 2000 and 3000; C returns an empty collection of orders.

But the complete collection will always be returned:

 select c from Customer c, in(c.customerOrders) o where o.cost>1000 

How can I do this in JPQL or in Hibernate in particular?

Answer 1

The sent request is equivalent

 select c from Customer c inner join c.customerOrders o where o.cost > 1000 

which simply returns all customers who have at least one order with a value in excess of 1000.

I would suggest the reverse combination and selection of orders - this is semantically the same, but structurally different from your desired result:

 select o from CustomerOrder o where o.cost > 1000 

Hibernate now has a function other than JPA called Filter, which should do exactly what you are looking for - see here: http://www.hibernate.org/hib_docs/reference/en/html/filters.html

Answer 2

It seems like a bad idea (in terms of performance) is related to OneToMany.

But why this does not work: select o from CustomerOrder o where o.cost > 1000; and then the client retrieves from the list of results?

Answer 3

try it

 select c from Customer c join CustomerOrder o with o.cost > 1000 

He can return the client twice if he has two orders worth> 1000, for which you can make a group

 select c from Customer c join CustomerOrder o with o.cost > 1000 group by c