I have created a small application and I’m trying out access groups.
In my application I created a class called ‘Audit’. This class has a Many to Many relation to ‘User’. I created this relation in the CUBA studio via -> Association -> Many to Many, and the collection type being a set.
Now I want to formulate the following constraint for an access group:
The current user should only be able to see the ‘Audit’ row if he is assigned to the audit’s users collection.
The only idea I had was something like this:
:session$userId member of {E}.users
This can not work because on the one side we have the id, on the other side user objects.
Can you give me a hint how to formulate it correcty,?
Have you read the Audit section in the Full-scale CUBA application step by step guide? I believe you will find the answer there. No programming to be done.
Hello Edwin,
thanks you for your answer. Yes, I read the guide, but in that guide the scenario is different in that a ‘Mechanic’ references a single ‘User’, thus the constraint is formulated as: {E}.mechanic.user.id = :session$userId
(see page 82 of the mentioned document)
Because I’m using a collection I don’t know how to formulate the constraint.
I tried it and I get the following error when accessing the list view:
com.haulmont.cuba.core.global.RemoteException:
---
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Problem compiling [select e from servicetest$Audit e where e.users.id = :session$userId].
[40, 50] The state field path 'e.users.id' cannot be resolved to a valid type.
---
org.eclipse.persistence.exceptions.JPQLException:
Exception Description: Problem compiling [select e from servicetest$Audit e where e.users.id = :session$userId].
[40, 50] The state field path 'e.users.id' cannot be resolved to a valid type.
I see what you mean now. There is no user attribute in the AUDIT table as there is an AUDIT_USER_LINK table created for it which does not appear as an entity in dropdown box in the constraint screen. I wonder if you are able to join with this table in this constraint.
The problem with the join is that it always does an inner join, even if I write ‘left join’ in order to make an outer join.
Example:
audit a: user 1 assigned
audit b: no user assigend
if I do it as Edwin mentioned and formulate the where clause like u.id = :session$userId or 1=1
then only audit a is shown. Thus, it is always an inner join which does not work when I want to add another WHERE condition like in the sample above.
I have tried to reproduce your case with left join on Platform entities and it does work for me.
The result query really use left join.
My example (constraint on users):
JOIN CLAUSE
left join {E}.userRoles ur
WHERE CLAUSE
ur.role.id = '0c018061-b26f-4de2-a5be-dff348347f93' or {E}.login = 'admin'
Could you please set breakpoint at com.haulmont.cuba.core.sys.PersistenceSecurityImpl#applyConstraints(com.haulmont.cuba.core.Query)
and check the query.getQueryString() at the end of the method?
This is the result of my query: select e from servicetest$Audit e left join e.users u where u.id = :session$userId
So there is a left join, but no matter what I enter in my ‘or’ clause, I see only the audits that have the current user assigned. I just tried it with
join clause:
I would recommend checking what SQL is executed on the database.
Open Administration > Server Log > Options
Select “eclipselink.sql” in Logger, “DEBUG” in Level, and press Set
Go to View tab and press Show Tail and select Refresh checkbox.
You can also set DEBUG level for eclipselink.sql logger in tomcat/conf/logback.xml and restart the server. Then see tomcat/logs/app.log.
2016-04-22 16:03:59.200 DEBUG [http-nio-8080-exec-132/app-core/manager] com.haulmont.cuba.core.app.DataServiceQueryBuilder - Constraints applyed: select e from servicetest$Audit e left join e.users u where u.id = :session$userId or e.name = 'test'
This is still JPQL. When you see SQL, you can usually make a final conclusion about what is wrong with the query. Please set the “eclipselink.sql” logger to DEBUG and let us look at the SQL query.
SELECT LIMIT ? ? t1.ID AS a1, t1.CREATE_TS AS a2, t1.CREATED_BY AS a3, t1.DELETE_TS AS a4, t1.DELETED_BY AS a5, t1.NAME AS a6, t1.SOURCE_NUMBER AS a7, t1.TARGET_NUMBER AS a8, t1.UPDATE_TS AS a9, t1.UPDATED_BY AS a10, t1.VERSION AS a11
FROM SERVICETEST_AUDIT t1
LEFT OUTER JOIN SERVICETEST_AUDIT_USER_LINK t2 ON (t2.AUDIT_ID = t1.ID) JOIN SEC_USER t0 ON (t0.ID = t2.USER_ID)
WHERE (((t0.ID = ?) OR (t1.NAME = ?)) AND (t1.DELETE_TS IS NULL))
bind => [0, 50, e05dbdb2-8463-eccb-cc68-49bea397afbe, test]
I think the problem occurs because the second join (AUDIT_USER_LINK to SEC_USER) is not an outer join.
Which database do you use?
We have checked almost the same case with PostgreSQL and it did work fine. To localize the issue we need to know your database vendor and version.