Need help in writing access group constraints with inner query

Support
#1

Hi Everyone,

I am trying to write access group constraints for an entity.
The constraint is an inner query which looks similar to like the below one:

SELECT *
FROM Product
WHERE Id IN (SELECT ProductId
FROM OrderItem
WHERE Quantity > 100)

I am trying to write the constraint in Groovy script but i am getting error.
Could anyone please help me in writing groovy script similar to the above inner query. I tried using wizard but it didnt help.

Thanks in advance for the help.

#2

Hi,

First, you should think twice before coding any non-trivial operations in a Groovy constraint script.

  1. For the constraints with check in memory, the conditions are specified using Groovy expressions. The expressions are executed for every entity in the checked graph of objects, and if the entity does not match the conditions, it is filtered from the graph.

https://doc.cuba-platform.com/manual-7.1/constraints.html

So when you load 50 entities in the browser, you will make 50 addititional queries to the database if you execute query in a Groovy READ in-memory constraint.

Regarding the implementation - you can just write a multi-line Groovy script that can use EntityManager / Query and should return Boolean. For example:

import com.haulmont.cuba.core.Persistence
import com.haulmont.cuba.core.global.AppBeans
import com.haulmont.cuba.core.global.UserSessionSource

def entityId = {E}.id
def userId = AppBeans.get(UserSessionSource).currentOrSubstitutedUserId()

def em = AppBeans.get(Persistence).entityManager
def count = em.createQuery('select count(e) from demo17_Foo e where e.id = :entityId and e.author.id = :userId')
    .setParameter('entityId', entityId)
    .setParameter('userId', userId)
    .singleResult
return count > 0

To make use of IDEA coding assistance features when you are writing such scripts, you can create temporary .groovy script file inside of the app-core sources, for example:

image
image1502×407 71.4 KB