CODEWITHSUNDEEP
neo4jcypherspring-data-neo4j
Rajeev kumar
Rajeev kumar

Reputation: 1

Cypher query returning null values even when the pattern is available in database

Is something wrong with this cypher query

MATCH (owner:SidNode)<-[:OWNED_BY]-(acl:AclNode)-[:SECURES]->(class:ClassNode) 
OPTIONAL MATCH (acl)<-[:COMPOSES]-(ace:AceNode)-[:AUTHORIZES]->(sid:SidNode) 
WITH acl, ace, owner, sid, class 
    WHERE  (acl.objectIdIdentity = {objectIdIdentity1} AND class.className = {className1})   
RETURN 
owner.principal AS aclPrincipal, 
owner.sid AS aclSid, 
acl.objectIdIdentity AS objectIdIdentity, 
ace.aceOrder AS aceOrder, 
ID(acl) AS aclId, 
acl.parentObject AS parentObject, 
acl.entriesInheriting AS entriesInheriting, 
ID(ace) AS aceId, ace.mask AS mask, 
ace.granting AS granting, 
ace.auditSuccess AS auditSuccess, 
ace.auditFailure AS auditFailure, 
sid.principal AS acePrincipal, 
sid.sid AS aceSid, 
class.className AS className  
ORDER BY acl.objectIdIdentity ASC, ace.aceOrder ASC

It is returning null values for ace nodes even though there are multiple nodes available in graph db. But some times it is returning proper values like 4 rows if there are 4 ace nodes in db. code i am writing is about spring security acl reference link: https://github.com/shazin/spring-security-acl-neo4j/blob/master/src/main/java/org/springframework/security/acls/neo4j/Neo4jLookupStrategy.java

Please suggest modifications.

Upvotes: 0

Views: 1483

Answers (2)

jjaderberg
jjaderberg

Reputation: 9952

You bind the ace nodes in an optional match. When that optional match fails to match something, ace will be null.

If you think the optional match ought to be successful in cases when it is not, maybe you could provide an example. A good way to do so is to create a small sample graph at http://console.neo4j.org

The conditions in your WHERE clause look like they belong with your MATCH clause. You might want to move the WHERE clause up and you can then remove the WITH altogether. It won't affect nulls, but it will make your query more efficient and more readable. (Also, you don't need parentheses in your WHERE clause.)

MATCH (owner:SidNode)<-[:OWNED_BY]-(acl:AclNode)-[:SECURES]->(class:ClassNode)
WHERE acl.objectIdIdentity = {objectIdIdentity1} AND class.className = {className1}
OPTIONAL MATCH (acl)<-[:COMPOSES]-(ace:AceNode)-[:AUTHORIZES]->(sid:SidNode)
RETURN ...

Upvotes: 0

Supamiu
Supamiu

Reputation: 8731

Your problem comes from OPTIONAL MATCH, according to Neo4j's documentation, OPTIONAL MATCH returns NULLif the property or the element is not found.

You are getting NULL values because of this. If your acl node doesn't have any ace node related to him, the node will be replaced with NULL

Upvotes: 0

Related Questions