CODEWITHSUNDEEP
neo4jspring-datacypherspring-data-neo4j
alexanoid
alexanoid

Reputation: 25862

Neo4j Cypher query with null or not null value

In my Spring Data Neo4j project I have a following entities:

@NodeEntity
public class Decision extends Commentable {

    private final static String CONTAINS = "CONTAINS";
    private final static String DEFINED_BY = "DEFINED_BY";

    private String name;

    @Relationship(type = DEFINED_BY, direction = Relationship.INCOMING)
    private Set<CriterionGroup> criterionGroups = new HashSet<>();

    @Relationship(type = DEFINED_BY, direction = Relationship.INCOMING)
    private Set<Criterion> criteria = new HashSet<>();

...

}

@NodeEntity
public class Criterion extends Authorable {

    private final static String CONTAINS = "CONTAINS";
    private final static String DEFINED_BY = "DEFINED_BY";

    private String name;

    @Relationship(type = CONTAINS, direction = Relationship.INCOMING)
    private CriterionGroup group;

    @Relationship(type = DEFINED_BY, direction = Relationship.OUTGOING)
    private Decision owner;

...

}

@NodeEntity
public class CriterionGroup extends Authorable {

    private final static String DEFINED_BY = "DEFINED_BY";
    private final static String CONTAINS = "CONTAINS";

    private String name;

    @Relationship(type = DEFINED_BY, direction = Relationship.OUTGOING)
    private Decision owner;

    @Relationship(type = CONTAINS, direction = Relationship.OUTGOING)
    private Set<Criterion> criteria = new HashSet<>();

...

}

I have a following SDN repository method:

@Query("MATCH (d:Decision)<-[:DEFINED_BY]-(c:Criterion) WHERE id(d) = {decisionId} and c.name = {name} RETURN c")
Criterion findCriterionDefinedByDecisionByName(@Param("decisionId") Long decisionId, @Param("name") String name);

Based on this query I can get Criterion that belongs to Decision and have a specific name.

Criterion in my domain model may (or may not)belong to CriterionGroup.

I need to extend this query in order to add one more condition for checking CriterionGroup associated with this Criterion. In another words I need to return Criterion with a specific {name} for a specific {decisionId} that belongs(or not in case of null value) to a provided {criterionGroupId}. In case of {criterionGroupId} == null I need to find Criterion that do not belong to any CriterionGroup.

I need something like this:

@Query("MATCH (d:Decision)<-[:DEFINED_BY]-(c:Criterion)...????????...... WHERE id(d) = {decisionId} and c.name = {name} RETURN c")
Criterion findCriterionDefinedByDecisionByName(@Param("decisionId") Long decisionId, @Param("name") String name, @Param("criterionGroupId") Long criterionGroupId);

Please help me to write this query.

Upvotes: 1

Views: 6855

Answers (1)

Frank Pavageau
Frank Pavageau

Reputation: 11735

This should work, and shouldn't be too slow either unless you have tens of thousands of CriterionGroups per Criterion:

MATCH (d:Decision)<-[:DEFINED_BY]-(c:Criterion)
WHERE id(d) = {decisionId} 
  AND c.name = {name}
OPTIONAL MATCH (c)<-[:CONTAINS]-(cg:CriterionGroup)
WITH c, extract(g IN collect(cg) | id(g)) AS cgIds
WHERE CASE
        WHEN {criterionGroupId} IS NULL THEN size(cgIds) = 0
        ELSE {criterionGroupId} IN cgIds
      END
RETURN c

Alternatively, you could have 2 methods in your Repository, to manage each case directly, using

MATCH (d:Decision)<-[:DEFINED_BY]-(c:Criterion)
WHERE id(d) = {decisionId} 
  AND c.name = {name}
  AND NOT (c)<-[:CONTAINS]-(:CriterionGroup)
RETURN c

when criterionGroupId is null, and

MATCH (d:Decision)<-[:DEFINED_BY]-(c:Criterion),
      (c)<-[:CONTAINS]-(cg:CriterionGroup)
WHERE id(d) = {decisionId} 
  AND c.name = {name}
  AND id(cg) = {criterionGroupId}
RETURN c

otherwise.

Upvotes: 2

Related Questions