I have run into an issue when adding a constraint definition which applies multiple conditions using an OR statement. The where clause I am using is as follows:
(select u.person.id from xam$ExtUser u where u.id = :session$userId) = {E}.assignedSupervisor.id
OR (select u.person.id from xam$ExtUser u where u.id = :session$userId) = {E}.assignedPerson.id
The issue is that records which contain a null value in either the AssignedSupervisor or Assigned Person field do not show up in the results. I believe the issue may reside in the SQL that is generated joining to the Person table. There are two joins to this table created and the results are filtered using the below statement:
XAM_PERSON t4,
XAM_PERSON t3
AND (((t4.ID = t1.ASSIGNED_SUPERVISOR_ID) AND (t4.DELETE_TS IS NULL)) AND ((t3.ID = t1.ASSIGNED_PERSON_ID) AND (t3.DELETE_TS IS NULL))))
I think the fact that this statement is using ANDs and the joins are not specified as left joins is causing the records containing a NULL value in either of the two fields to get filtered out.
Hi,
EclipseLink uses INNER JOIN for path expressions like: {E}.assignedSupervisor.id
As a workaround, you can write security constraints with left join. e.g:
JOIN clause: left join {E}.assignedSupervisor s left join {E}.assignedPerson p
WHERE clause: (select u.person.id from xam$ExtUser u where u.id = :session$userId) = s.id OR (select u.person.id from xam$ExtUser u where u.id = :session$userId) = p.id
I need to add another level of complexity to this query. The things I’ve added are:
Added to Join Clause: left join {E}.assignedTeam t
Added to Where Clause: OR (select u.person from xam$ExtUser u where u.id = :session$userId) MEMBER OF t.people
After adding these statements I am getting the error below. You will notice a few additional statements in the query. These are due to the system appending the constraint to a query used to populate a table on our main window. I am not sure how to interpret the error as the query syntax looks correct. Is this perhaps something to do with the MEMEBER OF statement?
Exception Description: The field [XAM_PERSON.ID] in this expression has an invalid table in this context.
Query:
ReadAllQuery(referenceClass=WorkOrder jpql=
"select e from xam$WorkOrder e
join xam$ExtUser u
left join e.assignedSupervisor s
left join e.assignedPerson p
left join e.assignedTeam t
where (u.id = :session_userId and u.person.id = e.assignedPerson.id and e.state not in ( 'Completed', 'Closed', 'Canceled')) and ((select u.person.id from xam$ExtUser u where u.id = :session$userId) = s.id OR (select u.person.id from xam$ExtUser u where u.id = :session$userId) = p.id OR e.createdBy = :session$userLogin OR (select u.person from xam$ExtUser u where u.id = :session$userId) MEMBER OF t.people) order by e.workOrderId")