Subquery in Cypher

[sharmas_shuchi]

am very new to Neo4j so need some assistance I am trying to to execute following query in NeO4j The idea is extract all the users who used to post on ['collapse' ,'science' ,'politics'] subreddits before 01 Jan 2020 and are now posting on ['covid19','china_flu','coronavirus'] subreddits. This syntax is not working u.username IN {match (c:User).How can get results of the subquery to filter the results from outer query

Raltionship is
user ->Submit->Post->Submit->Subreddit

match (s:Subreddit)--(p:Post)--(u:User) 
where toLower(p.title) =~ '.*corona.*' 
and s.display_name IN ['covid19','china_flu','coronavirus']
and p.created_utc_str >= '2020-01-01'
and p.created_utc_str <= '2020-01-30'
and u.username IN {match (c:User)-[:Submitted]->(b:Post)-[:Submitted]->(t:Subreddit)
                    where t.display_name IN ['collapse' ,'science' ,'politics'] 
                    and p.created_utc_str < '2020-01-01'
                    return distinct c.username}
return distinct  s,p,u
order by p.created_utc_str 

[Cobra]

Hello

You mus add a CALL front of the sub-query
Doc: https://neo4j.com/docs/cypher-manual/current/clauses/call-subquery/

So your request should be like this:

MATCH (s:Subreddit)--(p:Post)--(u:User) 
WHERE toLower(p.title) =~ '.*corona.*' 
AND s.display_name IN ['covid19', 'china_flu', 'coronavirus']
AND toInteger(p.created_utc_str) >= 20200101
AND toInteger(p.created_utc_str) <= 20200130
AND u.username IN CALL {MATCH (c:User)-[:Submitted]->(b:Post)-[:Submitted]->(t:Subreddit)
                        WHERE t.display_name IN ['collapse', 'science', 'politics'] 
                        AND toInteger(p.created_utc_str) < 20200101
                        RETURN DISTINCT c.username}
RETURN DISTINCT s, p, u
ORDER BY p.created_utc_str 

I added a correction when you compare the dates:)

[sharmas_shuchi]

Hi Maxime
Thanx a ton for your feedback.
But I am getting this issue now in this piece of the code
AND u.username IN CALL {MATCH (c:User)-[:Submitted]->(b:Post)

Invalid input '(': expected whitespace, comment, ':', ',' or '}' (line 6, column 32 (offset: 264))
"AND u.username IN CALL { MATCH (c:User)-[:Submitted]->(b:Post)-[:Submitted]->(t:Subreddit)"

[Cobra · Accepted solution]

Yes I don't think it's possible to use sub-query like this but try to add a collect like this:

MATCH (s:Subreddit)--(p:Post)--(u:User) 
WHERE toLower(p.title) =~ '.*corona.*' 
AND s.display_name IN ['covid19', 'china_flu', 'coronavirus']
AND toInteger(p.created_utc_str) >= 20200101
AND toInteger(p.created_utc_str) <= 20200130
AND u.username IN CALL {MATCH (c:User)-[:Submitted]->(b:Post)-[:Submitted]->(t:Subreddit)
                        WHERE t.display_name IN ['collapse', 'science', 'politics'] 
                        AND toInteger(p.created_utc_str) < 20200101
                        RETURN collect(DISTINCT c.username)}
RETURN DISTINCT s, p, u
ORDER BY p.created_utc_str

Else there is a solution with WITH clause:

MATCH (c:User)-[:Submitted]->(b:Post)-[:Submitted]->(t:Subreddit)
WHERE t.display_name IN ['collapse', 'science', 'politics'] 
AND toInteger(p.created_utc_str) < 20200101
WITH collect(DISTINCT c.username) AS names
MATCH (s:Subreddit)--(p:Post)--(u:User) 
WHERE toLower(p.title) =~ '.*corona.*' 
AND s.display_name IN ['covid19', 'china_flu', 'coronavirus']
AND toInteger(p.created_utc_str) >= 20200101
AND toInteger(p.created_utc_str) <= 20200130
AND u.username IN names
RETURN DISTINCT s, p, u
ORDER BY p.created_utc_str 

[sharmas_shuchi]

Hi Maxime

It works.
Many thanks for your help

[Cobra]

Hi, it's a pleasure to hear that