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 8/19/2013 5:32 PM, Laird Nelson wrote:

On Mon, Aug 19, 2013 at 4:18 PM, Andrei Ilitchev
<andrei.ilitchev@xxxxxxxxxx <mailto: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's correct.
So the right (and short) answer for your original question would be:
instead of:
  AND (child IS EMPTY OR child.somethingOrAnother = 'someValue'...
use:
  AND (l.children IS EMPTY OR child.somethingOrAnother = 'someValue'...



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


_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top