problem with a query which has OPTIONAL MATCH in neo4j

Question

Consider the following schema, where orange nodes are of type Friend and pink node is of type fbUser.

The query that I am trying to write goes as follows:

I'm trying to match all nodes which have a specific name in a specific range of time and optionally related to each Friend node, every tagged_together relationship

So I wrote the following query:

MATCH x=(:fbUser {graph_information:['admin', 'facebook']})--(f:Friend) 
OPTIONAL MATCH y=(f)-[s:TAGGED_TOGETHER]-(d:Friend) 
WITH x,f,y,d,s 
WHERE  
    f.name=~ '(?i).*ruggeri.*' AND  
    f.timestamp>'2016-02-22' AND f.timestamp<'2020-02-22' AND
    d.timestamp>'2016-02-22' AND d.timestamp<'2020-02-22'
RETURN x,y

In this specific case,the output is correctly what I want, in fact I have this:

but if run the query with this line

WHERE  
        f.name=~ '(?i).*kirby.*' AND  
        f.timestamp>'2016-02-22' AND f.timestamp<'2020-02-22' AND
        d.timestamp>'2016-02-22' AND d.timestamp<'2020-02-22'

I expect this output

but I receive (no changes, no records)

Why?

What I've understand so far is that if an optional match doesn't have any results it returns null.

TheTeacher TheTeacher · Accepted Answer · 2020-02-22T13:04:21

Here ,

WHERE  
        f.name=~ '(?i).*kirby.*' AND  
        f.timestamp>'2016-02-22' AND f.timestamp<'2020-02-22' AND
        d.timestamp>'2016-02-22' AND d.timestamp<'2020-02-22'

When there are no tagged friends ,’d’ will be null and it cannot pass through these d.timestamp conditions

You can update your query like following to get the expected results ,

MATCH x=(:fbUser {graph_information:['admin', 'facebook']})--(f:Friend) 
OPTIONAL MATCH y=(f)-[s:TAGGED_TOGETHER]-(d:Friend) 
WITH x,f,y,d,s, case d when null then [] else [1] end as iterList 
WHERE  
    f.name=~ '(?i).*ruggeri.*' AND  
    f.timestamp>'2016-02-22' AND f.timestamp<'2020-02-22' AND ALL (x in iterList WHERE 
    d.timestamp>'2016-02-22' AND d.timestamp<'2020-02-22') 
RETURN x,y

I am creating an iteration list based on the d value . So , when it’s null , we won’t check the time stamps on d, since we are iterating through empty list . When d is not null, we will the time stamps on d .

problem with a query which has OPTIONAL MATCH in neo4j

1 Answers