On Mar 5, 1:57 am, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> > " join o.validRoles as vr"
> If you get a chance ... can you change this join to a "fetch join"...
I can't change it since I need the alias in the where clause, but I
can add the fetch:
" select distinct o from User as o" +
" left join fetch o.validRoles" +
" left join o.validRoles as vr" +
" where vr.name = :name");
SELECT DISTINCT t0.userid, t0.archivedate, t0.isarchived,
t0.authorizationLevel, t0.dateofhire, t0.email,
t0.firstname, t0.javascript, t0.lastname, t0.lastworkpatternstart,
t0.logindisableduntil, t0.loginlockedout,
t0.mi, t0.password, t0.passwordmodifiedon,
t0.roletimecreditdisplayunit, t0.schedulesharepasscode,
t0.swapcontactinfo, t0.swappreferences, t0.timecardid, t0.usercode,
t0.userlogin, t3.usertypeid, t3.name,
t3.printorder, t3.scheduleable, t0.workpatternlength, t4.userid,
t5.roleid, t5.breaklength, t5.isdisplaceable,
t5.endtime, t5.isexclusivewithrooms, t5.iconfileextension,
t5.iconpdfsize, t5.iconwebsize, t5.name,
t5.notificationtargets, t5.printcode, t5.printorder, t5.isrequired,
t5.rolelength, t6.rolepublicviewpolicyid,
t6.name, t7.roletypeid, t7.descr, t7.name, t7.printcode,
t5.showascover, t5.starttime, t8.approvalpolicyid,
t8.name, t5.istimetrackable, t9.usertypeid, t9.name, t9.printorder,
t9.scheduleable
FROM public.users t0
LEFT OUTER JOIN public.userroles t1 ON t0.userid = t1.userid
INNER JOIN public.usertypes t3 ON t0.usertypeid = t3.usertypeid
LEFT OUTER JOIN public.userroles t4 ON t0.userid = t4.userid
LEFT OUTER JOIN public.roles t2 ON t1.roleid = t2.roleid
LEFT OUTER JOIN public.roles t5 ON t4.roleid = t5.roleid
LEFT OUTER JOIN public.rolepublicviewpolicies t6 ON
t5.rolepublicviewpolicyid = t6.rolepublicviewpolicyid
LEFT OUTER JOIN public.roletypes t7 ON t5.roletypeid = t7.roletypeid
LEFT OUTER JOIN public.approvalpolicies t8 ON t5.swapapprovalpolicyid
= t8.approvalpolicyid
LEFT OUTER JOIN public.usertypes t9 ON t5.usertypeid = t9.usertypeid
WHERE (t2.name = ?)
ORDER BY t4.userid ASC
[params=(String) XX2_]
So we're down to one query, however the test fails now because it
returns 2 (of the same) users. The validRoles is still fully
populated, of course.
> Yes. I also think JPQL didn't help with it's need for distinct.
It doesn't seem to be working in this case. I do know there is a bug
with DISTINCT in OpenJPA but I'm not exactly sure of the nature.
> I think we want to do both ... and we want to make this as easy for people
> to use and understand. Hmmm.
The docs describe join() as a "join fetch" and Ebean will
automatically add joins to support select/where/order clauses. I
interpret this to mean that the join fetch is independent of the joins
used to satisfy the select/where/order. So this:
query.where().in("validRoles", myRoles)
has no visible relational join so I think it's just a criteria for
selecting my users. This:
query.join("validRoles").where().in("validRoles", myRoles)
adds the join fetch which is simply a "directive" (rather than a
relational join) to eagerly populate the validRoles. So the mechanism
that joins users to validRoles is not visible which leads to my
confusion. I'm not telling you either query should not filter
validRoles, just where my thinking was. I think we are in agreement
that you should be able to achieve both filtered and full, the issue
is it needs to be clear and easy.
/Daryl
