> If you get a chance ... can you change this join to a "fetch join"...

> I'd suggest the second query is a perhaps a lazy loading query. It is hard
> to say why it doubles up the joins though.  Feel free to post the full sql
> with the full select clause as it is hard to say which tables are actually
> being used to build the object graph (ie. I'm guessing the first select
> clause doesn't include any columns from t2).

> Assuming no columns from t2 are in the select clause of the first query it
> does make sense - and we can conclude that they are effectively applying the
> filter on the user (root level).

> > I'm thinking that people (like me) coming from JPA are going to be
> confused

> Yes. I also think JPQL didn't help with it's need for distinct. You will
> hardly ever need it in Ebean and I'm sure that will be confusing as well.

> To me this is actually coming down to the difference between *ToMany and
> *ToOne relationships. With *ToMany relationship there is a big difference
> between applying the filter to the parent ... vs applying the filter to the
> *ToMany... and perhaps that is not obvious.

> I think we want to do both ... and we want to make this as easy for people
> to use and understand. Hmmm.

> On Fri, Mar 5, 2010 at 3:24 PM, Daryl Stultz <kungfumachin...@gmail.com>wrote:

>> On Mar 4, 8:07 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
>> > > I was surprised - you always get the whole list.

>> > I'd suggest what you wanted to do was more like:
>> ... subquery illustration

>> > Personally I have a SQL bias and I'd suggest the reason you where
>> > suprised is because the ORM translated the query into sql that
>> > filtered the users (root level object)

>> I've been mulling the whole thing over since my last post and came to
>> the same conclusion. If my original query was an API for generating
>> SQL, I'd totally expect it to filter the children. I even put a
>> comment in my code that I might have to convert the criteria to a
>> subquery.

>> I'm thinking that people (like me) coming from JPA are going to be
>> confused, but I'm not willing to say your current implementation is
>> not the right one. If you do implement JPQL some day, though, I think
>> the difference will be glaring. Maybe people will use only one
>> approach or the other...

>> > That is, when you do the JPA query and see the generated SQL I'd
>> > expect to see a subquery used to limit the users -

>> Yes that's what I expected to see, and I'm sure I've seen it in some
>> cases, but I can't dig one up quickly. My first attempt was straight
>> JPA with a join fetch. This produced a double cartesian of the root
>> with the children, even with DISTINCT. Anyway, here's the test case:

>> User user = setup.insertUser("user1");
>> Role role1 = setup.insertRole("role1");
>> user.getValidRoles().add(role1);
>> Role role2 = setup.insertRole("role2");
>> user.getValidRoles().add(role2);
>> user.save();

>> List<Integer> roleIds = new ArrayList<Integer>();
>> roleIds.add(role1.getId());

>> Query query = ExecutionResources.getEntityManager().createQuery(
>>                                                " select distinct o from
>> User as o" +
>>                                                " join o.validRoles as vr"
>> +
>>                                                " where vr.name = :name");
>> query.setParameter("name", "role2");
>> JpaUtils.addFetchFields(query, User.class, "validRoles");

>> List<User> result = query.getResultList();
>> assertEquals(1, result.size());
>> User resultUser = result.get(0);
>> assertEquals(user, resultUser);
>> assertEquals(2, resultUser.getValidRoles().size());

>> JpaUtils.addFetchFields takes advantage of fetch plan manipulation to
>> eager fetch validRoles. The result is 2 queries rather than the
>> subquery. OpenJPA tends to generate more queries than I think it
>> should.

>> SELECT DISTINCT t0.userid, ...
>> FROM public.users t0
>> INNER JOIN public.userroles t1 ON t0.userid = t1.userid
>> INNER JOIN public.roles t2 ON t1.roleid = t2.roleid
>> WHERE (t2.name = ?) [params=(String) role2]

>> SELECT DISTINCT t0.userid...
>> FROM public.users t0
>> INNER JOIN public.userroles t1 ON t0.userid = t1.userid
>> INNER JOIN public.userroles t3 ON t0.userid = t3.userid
>> INNER JOIN public.roles t2 ON t1.roleid = t2.roleid
>> INNER JOIN public.roles t4 ON t3.roleid = t4.roleid
>> WHERE (t2.name = ?)
>> ORDER BY t0.userid ASC [params=(String) role2]

>> It looks a bit whacky to me, double joining the child tables. Not sure
>> this is all that helpful.

>> > and the question
>> > becomes ... if you want to end up with a sql subquery... why not use
>> > an subquery with the ORM query in the same way?

>> Yup, I hear ya. Looks like that's where I'm going...

>> > Yup - that is going to be a bug - but firstly I want to get to the
>> > bottom of the *ToMany filtering. Specifically I want to check the sql
>> > that the jpa query generates.

>> Just so we're clear, the default join/fetch produces a filtered child
>> set, the default lazy load produces a full set and the
>> JoinConfig().query() produces a full set.

>> /Daryl