CODEWITHSUNDEEP
neo4jcyphergraph-databasesmemgraphdb
Kiran
Kiran

Reputation: 3047

Neo4J Cypher: create relationship only if destination node exists

I am trying to load a csv file that and as part of import try to create a relationship if the foreign key fields are resolved.

OPTIONAL MATCH (d1:Destination1 { id: line[2], name: line[3] })
OPTIONAL MATCH (d2:Destination2 { id: line[4], name: line[5] })
MERGE (n:Source {id: line[0])-[:MY_RELATIONSHIP]->(d1)
MERGE (n:Source {id: line[0])-[:MY_RELATIONSHIP]->(d2)

this query fails when either of d1 or d2 are not resolved. Is there a way to have a IF THEN option to create a link only if the destination exists. I see even WHERE clause is not allowed in the MERGE statement.

UPDATE 1

Looks like I over simplified my query, to give better context, here is what I am planning to achieve:

LOAD CSV FROM 'file:///MyData.csv' AS line
MATCH (n:BOM { Material: line[0]})
WITH n, line
MATCH (s:Supplier { name:line[2]})
MATCH (t:Type { name: line[7]})
MATCH (g1:Grade { number: line[6]})
MATCH (g2:Grade { number: line[5]})
MERGE (n)-[:HAS_SUPPLIER]->(s)
MERGE (n)-[:MATERIAL_TYPE]->(t)
MERGE (n)-[:HAS_GRADE{priority:1}]->(g1)
MERGE (n)-[:HAS_GRADE{priority:2}]->(g2)

I notice that if the query fails to find either g1 or g2 it fully fails to update other relationships. I had to resort to Optional Match but then the Merge fails

Upvotes: 0

Views: 397

Answers (1)

Tomaž Bratanič
Tomaž Bratanič

Reputation: 6514

If the only thing you are doing is creating a relationship between existing nodes, then simply change the OPTIONAL MATCH to MATCH clause

MATCH (d1:Destination1 { id: line[2], name: line[3] })
MATCH (d2:Destination2 { id: line[4], name: line[5] })
....

You can think of it as this, if a MATCH clause fails to retrieve any data, then the query execution stops for that particular row. In a sense it is doing the same as:

OPTIONAL MATCH (d1:Destination1 { id: line[2], name: line[3] })
OPTIONAL MATCH (d2:Destination2 { id: line[4], name: line[5] })
WITH d1, d2
WHERE d1 IS NOT NULL and d2 IS NOT NULL
....

If you need to do other things as well, you can resort to FOREACH tricks or subqueries.

LOAD CSV FROM 'file:///MyData.csv' AS line
MATCH (n:BOM { Material: line[0]})
WITH n, line
MATCH (s:Supplier { name:line[2]})
MATCH (t:Type { name: line[7]})
OPTIONAL MATCH (g1:Grade { number: line[6]})
OPTIONAL MATCH (g2:Grade { number: line[5]})
MERGE (n)-[:HAS_SUPPLIER]->(s)
MERGE (n)-[:MATERIAL_TYPE]->(t)
FOREACH (_ in CASE WHEN g2 is not null and g1 is not null THEN [1] ELSE [] END | 
  MERGE (n)-[:HAS_GRADE{priority:1}]->(g1)
  MERGE (n)-[:HAS_GRADE{priority:2}]->(g2))

FOREACH trick has been around since forewer (https://data-xtractor.com/blog/databases/neo4j-cypher-hacks/#4_Conditional_Execution_with_8230FOREACH)

Maybe a more clean version would be to use a subquery. I think the following should work:

LOAD CSV FROM 'file:///MyData.csv' AS line
MATCH (n:BOM { Material: line[0]})
WITH n, line
MATCH (s:Supplier { name:line[2]})
MATCH (t:Type { name: line[7]})
OPTIONAL MATCH (g1:Grade { number: line[6]})
OPTIONAL MATCH (g2:Grade { number: line[5]})
MERGE (n)-[:HAS_SUPPLIER]->(s)
MERGE (n)-[:MATERIAL_TYPE]->(t)
WITH n, g1, g2
CALL {
  WITH n, g1, g2
  WITH *
  WHERE g1 IS NOT NULL and g2 IS NOT NULL 
  MERGE (n)-[:HAS_GRADE{priority:1}]->(g1)
  MERGE (n)-[:HAS_GRADE{priority:2}]->(g2)}

You can also obviously create two FOREACH subqueries or two subqueries if you want to link to grade1 if it exists but grade 2 does not exists.

LOAD CSV FROM 'file:///MyData.csv' AS line
MATCH (n:BOM { Material: line[0]})
WITH n, line
MATCH (s:Supplier { name:line[2]})
MATCH (t:Type { name: line[7]})
OPTIONAL MATCH (g1:Grade { number: line[6]})
OPTIONAL MATCH (g2:Grade { number: line[5]})
MERGE (n)-[:HAS_SUPPLIER]->(s)
MERGE (n)-[:MATERIAL_TYPE]->(t)
FOREACH (_ in CASE WHEN g1 is not null  THEN [1] ELSE [] END | 
  MERGE (n)-[:HAS_GRADE{priority:1}]->(g1))
FOREACH (_ in CASE WHEN g2 is not null  THEN [1] ELSE [] END | 
  MERGE (n)-[:HAS_GRADE{priority:2}]->(g2))

Upvotes: 1

Related Questions