I have a Customer collection, and each customer has a collection of orders.

How do I retrieve ONLY those customers with orders? I can't see how to qualify my fetch to only retrieve those customers with orders?

I have tried fetching all customers and using a prefetchpath on orders in the fetch. Then I can copy only those customers with orders to a different collection. It's just slow. There MUST be a better way (ie fetch ONLY those customers with orders)...

Thanks!

Chris

fetching only those customers with orders is not that hard It's similar to:

select distinct customer.*
from customer inner join order on customer.customerid = order.orderid

or:

select *
from customer 
where customerid in
(
select customerid from order
)

On most databases, this will result in the same execution plan, so it won't matter which one you'll pick, though the first is easier to program. Though, if customer has a BLOB/text/image field, distinct can't be applied and the subquery approach is much faster.

so for the first method, pass the customer - order relation in a relationcollection (or RelationPredicateBucket if you're using adapter). For the second query, produce a FieldCompareSetPredicate in a predicateexpression