FilterMany

This applies a filter on the beans returned for a OneToMany or ManyToMany relationship.

For example, Customer has OneToMany Orders. Instead of fetching ALL the orders for the customers being fetched we instead only want to fetch the new orders they placed since last week.

We use filterMany() to apply the filter to the orders fetched for each customer.

filterMany with simple string expression

// query customers fetching 'new orders since last week' ...
List<Customer> customers
  = new QCustomer()
  .name.istartsWith("Rob")
  .orders.filterMany("status = ? and orderDate > ?", Order.Status.COMPLETE, lastWeek)
  .findList();

In the above example are using a string expression with ? as bind parameter placeholders. This is a relatively easy way to add a simple expression.

Below is the same query where instead of using a string expression we create an ExpressionList to use for the filterMany.

filterMany with ExpressionList expression

// filter orders - status COMPLETE since last week
ExpressionList<Order> filterOrders = new QOrder()
  .status.eq(Order.Status.COMPLETE)
  .orderDate.greaterThan(lastWeek)
  .getExpressionList();


// query customers fetching 'new orders since last week' ...
List<Customer> customers
  = new QCustomer()
  .name.istartsWith("Rob")                   // (1) filter customers
  .orders.filterMany(filterOrders)           // (2) filter the orders for those customers
  .findList();

Example

Find customers and their contacts. Filter the contacts to only the contacts whose first name starts with 'Rob'.

List<Customer> customers =
    new QCustomer()
    .status.eq(Status.NEW)
    .contacts.filterMany("firstName istartsWith ?", "Rob")
    .findList();