Edit: Jump to the last Cypher statement below for the final answer. Otherwise, feel free to read thru the saga of discovery
I think the issue is that in the first query the entire WHERE clause is only being applied to the WITH. In the Cypher below I broke the WHERE clause from the first query into two WHEREs, one for the MATCH and one for the WITH. Hopefully this will give the expected results.
MATCH
(n:User {userId: 1234}),
(n_0:User {userId: 3345}),
(n_1:Group {groupId: 8765}),
(n_1_0:Event {eventId:3456})
WHERE (n)-[:PING {someProp:true}]->(n_0)
AND (n)-[:JOIN {someProp:"cool"}]->(n_1)
AND (n_1)-[:PUBLISH {otherProp: "Hello"}]->(n_1_0)
WITH n, n_0, n_1, n_1_0
OPTIONAL MATCH
(n)-->(n_rel),
(n_0)-->(n_0_rel),
(n_1)-->(n_1_rel),
(n_1_0)-->(n_1_0_rel)
WITH
n, count(n_rel) AS n_count,
n_0, count(n_0_rel) AS n_0_count,
n_1, count(n_1_rel) AS n_1_count,
n_1_0, count(n_1_0_rel) AS n_1_0_count
WHERE
n_count = 2
AND n_0_count = 0
AND n_1_count = 1
AND n_1_0_count = 0
RETURN n
Using the Movies graph I came up with this query. I believe it's roughly equivalent to your query and follows the same pattern I originally suggested. It has the same issue you found. When the OPTIONAL MATCH doesn't find any results then nothing is returned.
MATCH
(jamest:Person {name: "James Thompson"}),
(jessicat:Person {name: "Jessica Thompson"})
WHERE
(jamest)-[:FOLLOWS]->(jessicat)
WITH jamest, jessicat
OPTIONAL MATCH
(jamest)-[:REVIEWED]->(jamest_rev_rel), // James Thompson has 2 REVIEWED relationships
(jessicat)-[:FOLLOWS]->(jessicat_fol_rel) // Jessica Thompson has no outbound FOLLOWS relationships
WITH
jamest, count(jamest_rev_rel) AS jamest_rev_rel_count,
jessicat, count(jessicat_fol_rel) AS jessicat_fol_rel_count
WHERE
jamest_rev_rel_count = 2
AND jessicat_fol_rel_count = 0
RETURN jamest, jessicat // No results returned
I reworked the query into this. This one returns the expected results. It feels overly cumbersome but hopefully it'll give you something to work with. I'll keep tinkering with it.
MATCH
(jamest:Person {name: "James Thompson"}),
(jessicat:Person {name: "Jessica Thompson"})
WHERE
(jamest)-[:FOLLOWS]->(jessicat)
WITH jamest, jessicat
OPTIONAL MATCH
(jamest)-[:REVIEWED]->(jamest_rev_rel)
WITH
jessicat, jamest, count(jamest_rev_rel) as jamest_rev_rel_count
WHERE
jamest_rev_rel_count = 2 // James Thompson has 2 REVIEWED relationships
WITH jamest, jessicat
OPTIONAL MATCH
(jessicat)-[:FOLLOWS]->(jessicat_fol_rel)
WITH
jamest, jessicat, count(jessicat_fol_rel) AS jessicat_fol_rel_count
WHERE
jessicat_fol_rel_count = 0 // Jessica Thompson has no outbound FOLLOWS relationships
RETURN jamest, jessicat // The two nodes are returned as expected
The root of the issue turned out to be having one OPTIONAL MATCH with multiple, comma separated patterns versus multiple OPTIONAL MATCH statements. In the former, all of the separate patterns are considered to be a single pattern. Whereas in the latter they're distinct which is what this query needs. This SO question gives more details.
The query can be leaned down quite a bit though. This version gives the same results as the above and is a lot more readable in my opinion
MATCH
(jamest:Person {name: "James Thompson"}),
(jessicat:Person {name: "Jessica Thompson"})
OPTIONAL MATCH
(jamest)-[:REVIEWED]->(jamest_rev_rel)
OPTIONAL MATCH
(jessicat)-[:FOLLOWS]->(jessicat_fol_rel)
WITH
jessicat, jamest,
count(jamest_rev_rel) as jamest_rev_rel_count,
count(jessicat_fol_rel) as jessicat_fol_rel_count
WHERE
jamest_rev_rel_count = 2 // James Thompson has 2 outbount REVIEWED relationships
AND jessicat_fol_rel_count = 0 // Jessica Thompson has 0 outbound FOLLOWS relationships
RETURN
jamest, jessicat // The two nodes are returned as expected
