Self Join Access Group Constraint Problem

Support
#1

Hi,

I am having problem to get access group constraint working that involve a self join entity.

The entity is a self reference Employee entity with the following attributes:
id - Employee ID
name - Employee Name
manager1 - Manager number 1 of employee (many-to-one to Employee)
manager2 - Manager number 2 of employee (many-to-one to Employee)
user - User ID of employee

I want to limit the employee rows based on the user who is logged in. For example, if the user is Manager1 or Manager2, he/she will only see employee rows where they are manager plus his/her own employee data.

I use the access group constraint below but it’s not working. Appreciate if anyone can help.

( {E}.user.id = :session$userId or {E}.manager1.user.id = :session$userId or {E}.manager2.user.id = :session$userId )

1 Like
#2

Hi David,
The problem is that EclipseLink ORM by default joins the tables with the INNER JOIN, so you probably don’t see all expected results.
You can explicitly tell ORM to join with LEFT JOIN by using the “Join Clause” field in the constraint.
Join Clause:

left join {E}.user u left join {E}.manager1 m1 left join {E}.manager2 m2

Where Clause:

( u.id = :session$userId or m1.user.id = :session$userId or m2.user.id = :session$userId )
#3

Hi Konstantin,

Using the Join clause solved the problem.

Thank you very much. Saved me a lot of sleepless nights trying to debug the eclipselink.sql statements…

You’re the best!! Cheers mate.

#4

Yeah, ORM is usually very smart, but sometimes you have to give it a hint to do something right…