> 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