Hi All,
I am running into an issue where I am trying to make sure that all elements of a fetched collection in an entity satisfies a condition.
There is this table RgReportInstance model which holds a link to all the entity parameters it was generated for - private Set reportEntityInstances;
RgReportEntityInst has a reference to the EntityRow. Now EntityRow holds a set EntityRowLevelPerm which define which roles have permissions to the EntityRow. EntityRow also has a reference to its Entity which has a flag to specify whether or not it has row level permissions in which case there will be no entries to check for that entityRow.
So basicaly if the entity is not permissioned or if it is, and the EntityRow has right permissions for the user, it should return me those report instances.
SELECT
reportInstance
FROM
RgReportInstance reportInstance
LEFT JOIN FETCH reportInstance.reportRevision reportRevision
LEFT JOIN FETCH reportInstance.modifiedByUser
LEFT JOIN FETCH reportInstance.reportDefinition reportDefinition
LEFT JOIN FETCH reportDefinition.modifiedByUser
LEFT JOIN FETCH reportRevision.reportContentFilter
LEFT JOIN FETCH reportRevision.modifiedByUser
LEFT JOIN FETCH reportRevision.createdByUser
LEFT JOIN reportInstance.reportEntityInstances reportEntityInstance
LEFT JOIN reportInstance.entity entity
LEFT JOIN reportEntityInstance.entityRow entityRow
LEFT JOIN entityRow.permissions permissions
WHERE
reportInstance.id.tid = :tid
AND
reportInstance.reportDefinitionId != -1
AND
reportRevision.outputFormatCd != ‘SECTTHUMBNAIL’
AND
reportRevision.startDate > :startDate
AND
reportRevision.reportStatus != ‘7’
AND (entity. allowRowLevelSecurity=0 or permissions.id.roleId NOT IN (SELECT userRole.role.id.roleId from UserRole userRole WHERE userRole.id.tid =:tid AND userRole.id.userId
=:userId))
AND
reportInstance.reportDefinitionId
IN (
SELECT
reportDefinition.id.reportDefinitionId
FROM
ReportDefinition reportDefinition
LEFT JOIN reportDefinition.reportPermissions reportPermissions
WHERE
reportDefinition.id.tid = :tid
AND reportDefinition.reportType = ‘REPORT’
AND reportPermissions.id.roleId
IN (SELECT userRole.role.id.roleId from UserRole userRole WHERE userRole.id.tid =:tid AND userRole.id.userId =:userId)
)
ORDER BY reportRevision.updatedDate DESC
Eg : Instance 1 was generated for 1) Entity A, EntityRow A1
2) Entity B EntityRow B2
3) Entity C EntityRow C1
Instance 2 was generated for 1) Entity A, EntityRow A2
2) Entity B, ENtityRow B3
In this case If there are no entity level permissions for Entity A, But B and C have permissions for their rows B2 and C1 then only Instance 1 should be returned . However with the above join, it seems to be returning Instance 2 as well because of the condition in the query "allowRowLevelSecurity=0 or "
Basically it is doing a check on each entityrow and a long as one of them do not have permissioning on them it returns the Instance. I want the Iinstance after evaluating all the rows, and even if one row fails the permission check I dont want that Instance.
Any thoughts on what I can change in the query?