Create a virtual relationship after an OPTIONAL MATCH

[jon1]

I am trying to run a query that conditionally creates a virtual relationship upon an optional match.

A simplified version of the query is as follows:

MATCH (v:Venue)
OPTIONAL MATCH (v)-[:HOSTS_EVENT]->(e:Event)-[:IN_SERIES]->(s:Series)
WITH v, s
CALL apoc.create.vRelationship(v, "HOSTS_SERIES", {}, s) YIELD rel as vR
RETURN v, vR, s

Unfortunately this query is invalid (fails with Neo.DatabaseError.General.UnknownError)

I would like the query to return Venues that host a Series but also Venues that do not host any Events at all (hence the optional match). This is ultimately being used for graph visualisation so I do want the virtual relationship. Is there a way to construct a query that achieves this?

Thanks

[stefan_armbrust · Accepted solution]

If the OPTIONAL MATCH does not hit, s is null and you're trying to create a virtual relationship having null as end node. This is not possible.
I think you need to wrap your call apoc.create.vRelationship into a conditional, see https://neo4j.com/docs/labs/apoc/current/cypher-execution/conditionals/.

[jon1]

Great, that has worked. Thanks!

In case it helps anybody else, here is the fixed query

MATCH (v:Venue)
OPTIONAL MATCH (v)-[:HOSTS_EVENT]->(e:Event)-[:IN_SERIES]->(s:Series)
CALL apoc.when(s IS NOT NULL,
    "CALL apoc.create.vRelationship(v, 'HOSTS_SERIES', {}, s) YIELD rel as vR RETURN vR",
    "RETURN NULL as vR",
    {v:v, s:s}) YIELD value as vR
RETURN v, vR, s

[lavanya_kannan]

I faced a similar error with OPTIONAL MATCH:

CALL apoc.periodic.iterate(
	"MATCH  ((c1:citation) -[p1:probability]-> (t:lda_topic) <-[p2:probability]- (c2:citation))
	WHERE id(c1) < id(c2) AND (toFloat(p1.prob) + toFloat(p2.prob) > 1)
	WITH c1, c2, toFloat(p1.prob) as w1, toFloat(p2.prob) as w2, t, 2 as precision
	WITH c1, c2, w1, w2, t, 10^precision as factor
	WITH c1, c2, t, round(factor* (1/(2+w1+w2))) / factor as weight
	OPTIONAL MATCH (a:alias) -[:authored]-> (c1) 
	OPTIONAL MATCH (b:alias) -[:authored]-> (c2) 
	RETURN a, b, weight, t, c1.citation as citation1, c2.citation as citation2",
	"CREATE (a)-[e:through_topic]->(b)
	SET e.weight= weight, 
	 e.topic = t.entity_type,
	 e.citation1 = citation1,
	 e.citation2 = citation2", {batchSize:5000, iterateList:true, parallel:true})  
YIELD batches, total, errorMessages

and the error was

"Failed to create relationship `e`, node `b` is missing. If you prefer to simply ignore rows where a relationship node is missing, set 'cypher.lenient_create_relationship = true' in neo4j.conf": 235,
  "Failed to create relationship `e`, node `a` is missing. If you prefer to simply ignore rows where a relationship node is missing, set 'cypher.lenient_create_relationship = true' in neo4j.conf": 9660

When I tried to add 'cypher.lenient_create_relationship = true' in my conf, I ran into this error: https://github.com/neo4j/neo4j/issues/12059 but with Neo4j instance 3.5.

Currently, I made sure that my OPTIONAL MATCH does not throw any exception and am running the query with

CALL apoc.periodic.iterate(
	"MATCH  ((c1:citation) -[p1:probability]-> (t:lda_topic) <-[p2:probability]- (c2:citation))
	WHERE id(c1) < id(c2) AND (toFloat(p1.prob) + toFloat(p2.prob) > 1)
	WITH c1, c2, toFloat(p1.prob) as w1, toFloat(p2.prob) as w2, t, 2 as precision
	WITH c1, c2, w1, w2, t, 10^precision as factor
	WITH c1, c2, t, round(factor* (1/(2+w1+w2))) / factor as weight
	MATCH (a:alias) -[:authored]-> (c1) 
	MATCH (b:alias) -[:authored]-> (c2) 
	RETURN a, b, weight, t, c1.citation as citation1, c2.citation as citation2",
	"CREATE (a)-[e:through_topic]->(b)
	SET e.weight= weight, 
	 e.topic = t.entity_type,
	 e.citation1 = citation1,
	 e.citation2 = citation2", {batchSize:5000, iterateList:true, parallel:true})   
YIELD batches, total, errorMessages

The query seems to be running fine - just taking time.

I want to know the following:

1. Is issue https://github.com/neo4j/neo4j/issues/12059 resolved because "OPTIONAL MATCH" was changed so it will not raise a null?

2. Is "cypher.lenient_create_relationship" error above is being thrown because of OPTIONAL MATCH throwing a null

3. Do I need a WITH statement between the two MATCH statements

MATCH (a:alias) -[:authored]-> (c1) 
MATCH (b:alias) -[:authored]-> (c2) 

I tested this for small toy examples - but I am not sure what is happening for bigger instances.

@andrew.bowman requesting you to take a look at this.

Thanks,
Lavanya