Neo4j Cypher query null or IN

1
votes

I have a following cypher query:

MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) 
WHERE id(parentD) = {decisionId}  
RETURN ru, u, childD 
SKIP 0 LIMIT 100

Decision entity can belong to 0..N Tenant objects

@NodeEntity
public class Decision {
    private final static String BELONGS_TO = "BELONGS_TO";

    @Relationship(type = BELONGS_TO, direction = Relationship.OUTGOING)
    private Set<Tenant> tenants = new HashSet<>();

....

}

I need to extend the Cypher query above in order to return all childD where parentD and childD not belong to any of Tenant or belong to Tenant with IDs provided in {tenantIds} set. Please help me with this query.

2
neo4jcypherspring-data-neo4j

2 Answers

3
votes

Cypher is very expressive language, just follow your textual requirements...

MATCH (t:Tenant) WHERE ID(t) in {tenantIds}
WITH COLLECT(t) as tenants
MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) 
WHERE 
id(parentD) = {decisionId} 
AND
  // not belong to any of Tenant or belong to Tenant
  (not (parentD)-[:BELONGS_TO]-(:Tenant) OR  any(t in tenants WHERE (parentD)-[:BELONGS_TO]-(t)))
AND
  // not belong to any of Tenant or belong to Tenant 
  (not (childD)-[:BELONGS_TO]-(:Tenant)  OR  any(t in tenants WHERE (childD)-[:BELONGS_TO]-(t)))
RETURN ru, u, childD 
SKIP 0 LIMIT 100
2
votes

Use optional match to collect and test tenants:

MATCH (parentD) WHERE id(parentD) = {decisionId}
  OPTIONAL MATCH (parentD)-[:BELONGS_TO]->(T:Tenant) 
           WHERE NOT id(T) IN {tenantIds}
WITH parentD, collect(T) AS TC 
     WHERE size(TC) <= 0
MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) 
  OPTIONAL MATCH (childD)-[:BELONGS_TO]->(T:Tenant)
           WHERE NOT id(T) IN {tenantIds}
WITH childD, ru, u, collect(T) AS TC
     WHERE size(TC) <= 0
RETURN ru, u, childD 
SKIP 0 LIMIT 100