There is a long discussion on the google group about this topic:

http://groups.google.com/group/ebean/browse_thread/thread/f5b57df7e52ace7e

This is a 30 post discussion so I'm not going to repeat it all here.

The end result is that there are 2 interpretations of a where clause predicate on a 'many' property. 1 is the JPQL interpretation which means that the predicate is used to filter the top level objects. The 2nd interpretation current with Ebean is that it is a filter than can be applied to the fetch of the many itself.

In short we are changing Ebean to follow the JPQL interpretation, but looking to add "filter Many" type functionality. This is occasionally required when the *ToMany relationship has a high cardinality. For example, customers with 10,000's of orders - you may want to fetch customers with their orders.. but filter on the orders only fetching the new orders made in the last week.

List<MUser> list = Ebean.find(MUser.class)
    .join("roles")
    // the where on a 'many' (like orders) requires an 
    // additional join and distinct which is independent
    // of a fetch join (if there is a fetch join) 
    .where().eq("roles.roleName", "role2special")
    .findList();

... produces ...

<sql summary='MUser +many:roles' >
select distinct m.userid c0, m.user_name c1
        , mr.roleid c2, mr.role_name c3 
from muser m
left outer join mrole_muser mrz_ on mrz_.muser_userid = m.userid 
left outer join mrole mr on mr.roleid = mrz_.mrole_roleid 
join mrole_muser xrz_ on xrz_.muser_userid = m.userid 
join mrole xr on xr.roleid = xrz_.mrole_roleid  
where xr.role_name = ?  
order by m.userid
</sql>

Note that the table alias' starting with "X" ... are the extra joins required to support the "roles.roleName" = "role2special" predicate.

Ebean.find(Customer.class)
    .select("id,status")
    .where().eq("orders.status", Order.Status.NEW)
    .findList();

produces ...

<sql summary='Customer' >
select distinct c.id c0, c.status c1 
from o_customer c
join o_order xo on xo.kcustomer_id = c.id  
where xo.status = ? 
</sql>

Adding the fetch join .. you now see 2 joins to o_order

Ebean.find(Customer.class)
    .select("id,status")
    .join("orders")
    .where().eq("orders.status", Order.Status.NEW)
    .findList();

produces ...

<sql summary='Customer +many:orders' >
select distinct c.id c0, c.status c1
        , co.id c2, co.status c3, co.order_date c4, co.ship_date c5, coc.name c6, co.cretime c7, co.updtime c8, co.kcustomer_id c9 
from o_customer c
left outer join o_order co on co.kcustomer_id = c.id 
left outer join o_customer coc on coc.id = co.kcustomer_id 
join o_order xo on xo.kcustomer_id = c.id  
where co.order_date is not null and xo.status = ?  
order by c.id
</sql>

Fixed in HEAD.