c-leal opened a new issue, #14512:
URL: https://github.com/apache/grails-core/issues/14512
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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
