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 
Actually it works (though I thought it wouldn't):
"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.

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).




On 8/19/2013 3:47 PM, Laird Nelson wrote:

On Mon, Aug 19, 2013 at 3:41 PM, Andrei Ilitchev
<andrei.ilitchev@xxxxxxxxxx <mailto:andrei.ilitchev@xxxxxxxxxx>> wrote:

    Even called "child" it still refers to a collection to be joined,
    not to a single collection member.


Now I'm even MORE confused!  :-)

The JPA specification says:

    For example, the query

    SELECT DISTINCT o
    FROM Order o JOIN o.lineItems l
    WHERE l.product.productType = ‘office_supplies’

    can equivalently be expressed as follows, using the IN operator:

    SELECT DISTINCT o
    FROM Order o, IN(o.lineItems) l
    WHERE l.product.productType = ‘office_supplies’

    In this example, lineItems is the name of an association field whose
    value is a collection of instances of the abstract schema type
    LineItem. The identification variable l designates a member of this
    collection, a single LineItem abstract schema type instance. In this
    example, o is an identification variable of the abstract schema type
    Order.


Note the second-to-last sentence which says that l (lowercase L)
identifies a "single LineItem abstract schema type instance".  Isn't
that what my "child" is?

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