Checking Null on Relationship/nested Node using Criteria doesn't generate the correct CYPHER query

[c-leal]

Hi everyone,

I am having this issue when I try to check Null on Relationship/nested Node using Criteria.
I want my query returns the list of AssessmentSubControls where completedBy is null.
AssessmentSubControls and User are my domain objects using many to one relationship.

Here is AssessmentSubControls (I just show the relevant code):

class AssessmentSubControl {

    static belongsTo = [
        // AssessmentSubControl -> [IS_COMPLETED_BY] -> User
        completedBy: User
        ...
    ]
    static mapping = {
        completedBy        type: "IS_COMPLETED_BY", cascade: 'save-update'
        ...
    }
    static constraints = {
        completedBy nullable: true
        ...
    }
...

Nothing special on User domain.

Here is my criteria with the isNull check:

        def assignedTasks = AssessmentSubControl.createCriteria().listDistinct() {
            eq('assignedTo', user)
            assessmentControl{
                assessment{
                    eq('id', currentAssessment.id)
                }
            }
            isNull('completedBy') 

It generates the following CYPHER which is not correct because n.completedBy IS NULL expression checks on the field of ASSESSES_SUBCONTROL Node (which doesn't exist) when it should actually check the relationship.

MATCH (n:AssessmentSubControl), (n)-[:IS_ASSIGNED_TO]->(m_0:User), (n)<-[:ASSESSES_SUBCONTROL]-(m_1:AssessmentControl), (m_1)<-[:ASSESSES_CONTROL]-(m_2:Assessment) WHERE ( ID(m_0)=2403 AND n.completedBy IS NULL AND ( ( ID(m_2)=2414 ) ) ) 
OPTIONAL MATCH(n)-[r4:IS_COMPLETED_BY]->(completedByNode:User) WITH n, assignedByIds, assignedToIds, collect(DISTINCT ID(completedByNode)) as completedByIds 
OPTIONAL MATCH(n)-[r6:IS_VALIDATED_BY]->(validatedByNode:User) WITH n, assignedByIds, assignedToIds, completedByIds, collect(DISTINCT ID(validatedByNode)) as validatedByIds 
RETURN n as data, completedByIds, validatedByIds

Here is the query that gives me the expected result, using Not Exists on the relationship:

MATCH (asc:AssessmentSubControl), 
(asc)<-[:ASSESSES_SUBCONTROL]-(ac:AssessmentControl), 
(ac)<-[:ASSESSES_CONTROL]-(assessment:Assessment)
WHERE ( 
  Not Exists((asc)-[:IS_COMPLETED_BY]->(:User))
) 
RETURN asc,ac,assessment

Am I supposed to use notExists method instead of IsNull in the criteria? Not sure how my criteria query will look.

Let me know if you need more details.

Thanks,
Cyril