Thanks for the sql ... there are no columns in the select from t1 and t2...
indicating they are additional joins added to support the where clause (
t2.name = ?). They need both t1 and t2 because it is a many to many
obviously.
> returns 2 (of the same) users
I'd suggest that is a bug because you put the distinct in your query. Note
there is a difference here with Ebean. With Ebean you don't need to specify
the distinct as the persistence context is used during the building of the
object graph ... and that by definition we can't have 2 (of the same) users.
To me this is like JPA implementations are not using the persistence context
during it's object graph construction which I find very strange (FYI:
Patrick Linskey made a comment in a presentation suggesting the need for
distinct in JPQL was a mistake).
> I interpret this to mean that the join fetch is independent of the joins
used to satisfy the select/where/order.
Yes - I see the confusion. The issues of 'independent of the joins' ...
really only pertains to the *ToMany.
> So the mechanism that joins users to validRoles is not visible which leads
to my confusion.
Yes - I get that ... I can totally see why it is confusing coming from the
'independent join' angle. I hadn't seen it from that angle before. Looking
at it that way I'd suggest... the extra join + sql distinct approach is a
direct implementation for that (aka the resulting sql is quite expected).
So now I'm thinking we can go two ways...
Option 1. stick with the current interpretation ... and force people to
use subqueries and the like
Option 2. change to use the same interpretation as JPQL ... and provide
some mechanism so that people that want to filter the many can do so.
There is quite a bit of merit in option 2 ... except we could break some
existing queries (but probably not that many...).
- We would be more consistent with JPQL
- I believe the need to filter the many is much less common. You more often
need to filter the parent objects.
Hmmm.
On Sat, Mar 6, 2010 at 2:34 AM, Daryl Stultz <kungfumachin...@gmail.com>wrote:
> 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
