How to filter collection in JPA/JPQL?

Question

I have two entities:

@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 those customers with their CustomerOrder.cost>1000. For example, there are three customers A, B and C. A has two orders with cost=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 the three customers: A returns the orders with cost=2000 only; B returns the orders with 2000 and 3000; C returns an empty orders collection.

But the following will always return the full collection:

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

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

Answer 1

The query posted is equivalent to

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

which simply returns all customers that have at least one order with cost greater than 1000.

I would suggest to inverse join and select orders - it's semantically the same but structurally different from your desired result though:

select o from CustomerOrder o where o.cost > 1000

Now, Hibernate has non-JPA feature called Filter that should accomplish exactly what you are looking for - see here: http://www.hibernate.org/hib_docs/reference/en/html/filters.html

Answer 2

Try this

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

It may return a customer twice if he has two orders having cost > 1000, for which you can do group by

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