Hello, I've got an association that is perhaps non-standard. I can't really reduce the situation to Foo and Bar, so I'll introduce my domain language. Suppose I'm scheduling people to work in a grocery store. I've got:
Users - people Roles - what they do (Cashier, Stocker) ScheduleDay - a calendar date with a "day of week (DOW)" component Assignment - a Role on a DOW, stockers work only Monday-Friday, for example ScheduledAssignment - a User and Role on a ScheduleDay It's possible "Fred" could be scheduled to work as Stocker on a Saturday. It's not valid, but it could happen. So I want to load all the ScheduledAssignments for a given day and determine if they are valid. There is no direct relationship between ScheduledAssignment and Assignment. In SQL I would do it like this: select ScheduleDays.date, Roles.name, Users.name, Assignments.assignmentId from ScheduledAssignments join Users on Users.userId = ScheduledAssignments.userId join Roles on Roles.roleId = ScheduledAssignments.roleId join ScheduleDays on ScheduleDays.scheduleDayId = ScheduledAssignments.scheduleDayId and ScheduleDays.scheduleDate = '2010-01-02' left join Assignments on Assignments.roleId = Roles.roleId and Assignments.dayTypeId = ScheduleDays.dayTypeId So the left join to Assignments makes use of 2 keys from 2 different tables (dayTypeId is "day of week"). It is significant that the criteria is in the left join phrase. If I do this in JPQL: select o.scheduleDay.date, o.role.name, o.user.name, a.id from ScheduledAssignment o, Assignment a where o.scheduleDay.date = :date and o.scheduleDay.dayType.id = a.dayType.id and o.role.id = a.role.id the criteria is in the where clause and the invalid ScheduledAssignments are filtered out. Since there is no direct relationship between ScheduledAssignment and Assignment (the Assignment is essentially inferred rather than associated) I can't left join it. My question is: can I solve this problem in JPQL? Is there some way, like in Oracle, to say the join criteria in the where clause is optional (putting the "+" sign somewhere - I don't actually know how to do it). Thanks. -- Daryl Stultz _____________________________________ 6 Degrees Software and Consulting, Inc. http://www.6degrees.com mailto:da...@6degrees.com
