On Mar 4, 8:07 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:

... subquery illustration

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...

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.

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

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