Neo4j Cypher query with multiple conditions

Question

In my SDN 4 project I have a following entitites:

@NodeEntity
public class Characteristic extends Authorable {

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

    private String name;

    private String description;

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

}

@NodeEntity
public class Decision extends Commentable {

    private final static String DEFINED_BY = "DEFINED_BY";

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

}

@RelationshipEntity(type = "DECISION_CHARACTERISTIC")
public class DecisionCharacteristic {

    @GraphId
    private Long id;

    @StartNode
    private Decision decision;

    @EndNode
    private Characteristic characteristic;

    private Object value;

}

I need to select a Decision nodes that match the specific characteristics.

I have created a following Cypher query:

MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) WHERE id(parentD) = {decisionId} MATCH (childD)-[rdc:DECISION_CHARACTERISTIC]->(characteristic:Characteristic) WHERE  ( ( (  id(characteristic) = 138 AND  (rdc.value > 15000.32)) AND (  id(characteristic) = 138 AND  (rdc.value < 50000.32)) ) AND (  id(characteristic) = 139 AND  (rdc.value = 'Commercial')) )  WITH childD, ru, u RETURN childD

but this query works wrong. I need to select a Decision nodes that for characteristic with id=138 has a value between 15000.32 and 50000.32 and for characteristic with id=139 has exact value = 'Commercial'.

Where I'm wrong in my query and how to transform it in order to get working as expected ?

UPDATED

I have a following nodes:

        DecisionCharacteristic neo4jPriceDecisionCharacteristic = new DecisionCharacteristic(neo4jDecision, priceCharacteristic, new Double(10000.32d));
        decisionCharacteristicRepository.save(neo4jPriceDecisionCharacteristic);

        DecisionCharacteristic oraclePriceDecisionCharacteristic = new DecisionCharacteristic(oracleDecision, priceCharacteristic, new Double(35000.2d));
        decisionCharacteristicRepository.save(oraclePriceDecisionCharacteristic);

        assertNotNull(neo4jPriceDecisionCharacteristic);
        assertNotNull(neo4jPriceDecisionCharacteristic.getId());

        DecisionCharacteristic neo4jLicenseDecisionCharacteristic = new DecisionCharacteristic(neo4jDecision, licenseCharacteristic, "Commercial");
        decisionCharacteristicRepository.save(neo4jLicenseDecisionCharacteristic);

        DecisionCharacteristic orientLicenseDecisionCharacteristic = new DecisionCharacteristic(orientDecision, licenseCharacteristic, "Free");
        decisionCharacteristicRepository.save(orientLicenseDecisionCharacteristic);

        DecisionCharacteristic oracleLicenseDecisionCharacteristic = new DecisionCharacteristic(oracleDecision, licenseCharacteristic, "Commercial");
        decisionCharacteristicRepository.save(oracleLicenseDecisionCharacteristic);

IDs:

priceCharacteristic ID: 138
licenseCharacteristic ID: 139

I'm trying to get a Decision nodes with a following query:

   MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) WHERE id(parentD) = {decisionId} MATCH (childD)-[rdc:DECISION_CHARACTERISTIC]->(characteristic:Characteristic) WHERE  ( id(characteristic) = 138 AND  ( id(characteristic) = 138 AND  (  (rdc.value > 15000.32)) AND (  (rdc.value < 50000.32)) ) OR (  id(characteristic) = 139 AND  (rdc.value = 'Commercial')) )  RETURN childD

I expect that only oracleDecision node matches this condition but it returns two nodes:

Neo4j
Oracle

Where I'm wrong ?

Answer 1

The main flaw in your query is using ANDs to create an impossible where clause. You are literally asking it to return values where the id is two different numbers, and where the rdc.value is multiple values simultaneously...impossible. You need to use ORs instead in the right places so you can match on one id OR another id. Also, we can improve on the rdc.value restriction.

MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) 
WHERE id(parentD) = {decisionId} 
MATCH (childD)-[rdc:DECISION_CHARACTERISTIC]->(characteristic:Characteristic) 
WHERE  (id(characteristic) = 138 AND (15000.32 < rdc.value < 50000.32)) 
OR (id(characteristic) = 139 AND (rdc.value = 'Commercial'))  
WITH childD, ru, u 
RETURN childD

Lastly you don't need the WITH clause at all since you're only returning childD. Unsure if that's because you forgot to return the other variables in the return, but if you're truly only returning childD, then you can eliminate your WITH clause, as well as the ru and u variables in your first line match. If :Decisions are always created by users, then you can trim your first match by getting rid of "-[ru:CREATED_BY]->(u:User)". This would make your query look like:

MATCH (parentD)-[:CONTAINS]->(childD:Decision) 
WHERE id(parentD) = {decisionId} 
MATCH (childD)-[rdc:DECISION_CHARACTERISTIC]->(characteristic:Characteristic) 
WHERE  (id(characteristic) = 138 AND (15000.32 < rdc.value < 50000.32)) 
OR (id(characteristic) = 139 AND (rdc.value = 'Commercial'))  
RETURN childD

EDIT

Ah, okay, it wasn't clear from your query that you want to match on two different types of characteristics and place restrictions on both. In that case a single match against a characteristic will not be enough. You'll need to specify them separately.

MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[:CREATED_BY]->(:User)
WHERE id(parentD) = {decisionId} 
MATCH (childD)-[pdc:DECISION_CHARACTERISTIC]->(priceChar:Characteristic) 
WHERE  (id(priceChar) = 138 AND (15000.32 < pdc.value < 50000.32))
MATCH (childD)-[ldc:DECISION_CHARACTERISTIC]->(licenseChar:Characteristic) 
WHERE (id(licenseChar) = 139 AND (ldc.value = 'Commercial'))  
RETURN childD

I've got to ask, are you really okay with modeling your graph this way? You have Characteristic nodes, but you're storing characteristic data on relationships instead of the nodes themselves. You're also using a single Characteristic node when it seems clear that you actually have separate types, like PriceCharacteristic and LicenseCharacteristic and others. Do you have requirements or good reasons to do this, or do you have freedom to make changes to how your graph data is represented?