If property exists the use it in the WHERE clause

userLpcq 0
Viewed 26

Suppose you have the following query:

OPTIONAL MATCH (a:A)-[aff:AFFECTS]->(prd:Product { name:"name1" })
WHERE aff.versionStart <= "10.1.1" <= aff.versionEnd 
RETURN a

This works fine, but my problem is that I don't always have the versionEnd property, so in those cases I would like to just check for aff.versionStart <= "10.1.1", meanwhile if it exists then check for aff.versionStart <= "10.1.1" <= aff.versionEnd. Is there any way of achieving this behavior?

Cause I know I can use IS NOT NULL to check if it exists, but I haven't figure out how I can say "if it's not null, then use it, otherwise skip it"

cypher neo4j
3 Answers

you can use COALESCE() and provide a value of which you are sure that the second part of your WHERE clause returns true

OPTIONAL MATCH (a:A)-[aff:AFFECTS]->(prd:Product { name:"name1" })
WHERE  aff.versionStart <= "10.1.1" <= COALESCE(aff.versionEnd, '99.9.9') 

RETURN a
Graphileon0

COALESCE() is easier to write and understand. If you want a case when else statement, then here it is

OPTIONAL MATCH (a:A)-[aff:AFFECTS]->(prd:Product { name:"name1" })
WHERE  
    aff.versionStart 
     <= 
       "10.1.1" 
     <= 
     (case when aff.versionEnd is NOT NULL 
      then aff.versionEnd else '99' end)
RETURN a
jose_bacoy0

This is the way of doing it, in which if it exists only then it will be used, otherwise ignored:

OPTIONAL MATCH (a:A)-[aff:AFFECTS]->(prd:Product { name:"name1" })
WHERE (aff.versionStart <= "10.1.1") AND (aff.versionEnd IS NULL OR "10.1.1" <= aff.versionEnd) 
RETURN a
Charchit Kapoor0
Related