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.

// 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();

The above ORM query uses 2 sql queries to build the graph.

-- Primary query - customers ...
select t0.id, t0.inactive, t0.name, ...
from customer t0
where lower(t0.name) like ? escape'|'          -- (1) filter customers
-- Secondary query - orders for those customers ...
select t0.customer_id, t0.id, t0.status, t0.order_date, t0.ship_date, ...
from orders t0
where (t0.customer_id) in (? )
  and t0.order_date > ?  and t0.status = ?    -- (2) filter the orders for those customers

Edit Page