Refer to Daryl's testcase.
Based on this conversation at http://groups.google.com/group/ebean/browse_thread/thread/81421bbed2bce1a7
Query subQuery = Ebean.find(RoleTimeCreditType.class); subQuery.where() .eq("roleTimeCredits.timeTrackingEntry.scheduledAssignment.scheduleDay.date", cal.getTime()) .eq("roleTimeCredits.timeTrackingEntry.scheduledAssignment.user.userType.id", 1);
produces this SQL:
select distinct r.roletimecredittypeid as c0, r.name as c1, r.descr as c2, r.printorder as c3 from public.roletimecredittypes r join public.roletimecredits xr on xr.roletimecredittypeid = r.roletimecredittypeid join public.timetrackingentries xt on xt.timetrackingentryid = xr.timetrackingentryid join public.scheduledassignments xs on xs.scheduledassignmentid = xt.scheduledassignmentid join public.scheduledays xs on xs.scheduledayid = xs.scheduledayid join public.roletimecredits rr on rr.roletimecredittypeid = r.roletimecredittypeid left outer join public.timetrackingentries rrt on rrt.timetrackingentryid = rr.timetrackingentryid left outer join public.scheduledassignments rrts on rrts.scheduledassignmentid = rrt.scheduledassignmentid left outer join public.users rrtsu on rrtsu.userid = rrts.userid where xs.scheduledate = ? and rrtsu.usertypeid = ?
The issue here was in SqlTreeAlias ... and it was not taking into account the 'many where' table alias (to validate that the table alias had not already been used).
After fixed I have decided to change to use a simpler mechanism based on incrementing an int ... t0, t1, t2 etc ... and u1, u2, u3 for table alias' used to support where clauses on 'many'.
fixed in HEAD.