Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Outer joined one-to-many question
On Mon, Aug 19, 2013 at 4:18 PM, Andrei Ilitchev <andrei.ilitchev@xxxxxxxxxx> wrote:
Actually it works (though I thought it wouldn't):

Thank goodness it works; it's what the specification says. :-)
 
"SELECT e FROM Employee e LEFT OUTER JOIN e.projects projects WHERE (e.projects IS EMPTY OR projects.teamLeader IS NULL)"

producing:

[EL Fine]: sql: SELECT t1.EMP_ID, t2.EMP_ID, t1.F_NAME, t1.GENDER, t1.L_NAME, t1.PAY_SCALE, t1.ROOM_NUM, t2.SALARY, t1.STATUS, t1.VERSION, t1.START_TIME, t1.END_TIME, t1.START_OVERTIME, t1.END_OVERTIME, t1.FORMER_COMPANY, t1.FORMER_END_DATE, t1.FORMER_START_DATE, t1.END_DATE, t1.START_DATE, t1.ADDR_ID, t1.DEPT_ID, t1.MANAGER_EMP_ID, t1.HUGE_PROJ_ID, t0.ID, t0.NAME, t0.DEPT_HEAD FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN (CMP3_EMP_PROJ t4 JOIN CMP3_PROJECT t3 ON (t3.PROJ_ID = t4.projects_PROJ_ID)) ON (t4.EMPLOYEES_EMP_ID = t1.EMP_ID) LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_SALARY t2 WHERE ((((SELECT COUNT(t5.PROJ_ID) FROM CMP3_PROJECT t5, CMP3_EMP_PROJ t6 WHERE ((t6.EMPLOYEES_EMP_ID = t1.EMP_ID) AND (t5.PROJ_ID = t6.projects_PROJ_ID))) = ?) OR (t3.LEADER_ID IS NULL)) AND (t2.EMP_ID = t1.EMP_ID))
        bind => [0]

So it checks condition (teamLeader IS NULL) for every project.

Well, er, actually t3.LEADER_ID IS NULL is the only place where LEADER_ID shows up in the SQL you quoted, and it's in an OR fragment...?

Brain is slow today, but doesn't that mean that if there exists any Project that is IN an Employee's projects collection with a NULL teamLeader association that this query will return true?

That is NOT the same as saying that the collection must be empty OR all Projects in the collection must have a NULL team leader.

The pattern I see for collection attribute is as follows:
if the alias is used then it's presumed to be a member of collection (projects.teamLeader);
if alias is not used - then it's a collection (e.projects IS EMPTY).

Yes; that's what the specification says.

Thanks again for your help.

Best,
Laird
 
--
http://about.me/lairdnelson

Back to the top