cancel
Showing results for 
Search instead for 
Did you mean: 

Chaining multiple conditional queries using Cypher/APOC

prrao
Node

I'm trying to solve a problem that's similar to this one. However, I'm not able to understand how to apply it to my specific case.

I have geographic location nodes/relationships in my graph as such:

(city:CITY) -[:A_CITY_OF]-> (state:STATE) -[:A_STATE_OF]-> (country:COUNTRY)

I'm trying to create relationships between a person node p:PERSON and its hometown AND current residence town. The problem is that in the data source, the CITY and STATE names are not always guaranteed to exist for each person (however, the COUNTRY name where the person lives, or is from, will always exist in the source). So in my workflow, I'd do the following:

  • Use existing graph of persons and city/state/country nodes (locations already have relationships between them as described above)
  • Match person node (based on person ID)
  • Match city/state/country node (based on location name from data source)
  • Create relationship between person and the given location (could be either city, state OR country, depending on what node was matched in previous line)

I've tried to do this using apoc.do.when and apoc.do.case but for some reason the chaining of two separate apoc calls isn't working as intended.

WITH p, residences AS res, hometowns as hom
 CALL apoc.do.case(
   [EXISTS (res.city), 'MATCH (c:CITY {city: res.city}) -[:A_CITY_OF]-> (s:STATE {state: res.state}) -[:A_STATE_OF]-> (co:COUNTRY {country: res.country}) WITH p, c MERGE (p) -[:LIVES_IN]-> (c)',
   NOT EXISTS (res.city) AND EXISTS (res.state), 'MATCH (s:STATE {state: res.state}) -[:A_STATE_OF]-> (co:COUNTRY {country: res.country}) WITH p, s MERGE (p) -[:LIVES_IN]-> (s)' ],
   'MATCH (co:COUNTRY {country: res.country}) WITH p, co MERGE (p) -[:LIVES_IN]-> (co)', {res: res, p: p} ) YIELD value
WITH value AS _, p, hom
 CALL apoc.do.case(
   [EXISTS (hom.city), 'MATCH (c:CITY {city: hom.city}) -[:A_CITY_OF]-> (s:STATE {state: hom.state}) -[:A_STATE_OF]-> (co:COUNTRY {country: hom.country}) WITH p, c MERGE (p) -[:IS_FROM]-> (c)',
   NOT EXISTS (hom.city) AND EXISTS (hom.state), 'MATCH (s:STATE {state: hom.state}) -[:A_STATE_OF]-> (co:COUNTRY {country: hom.country}) WITH p, s MERGE (p) -[:IS_FROM]-> (s)' ],
   'MATCH (co:COUNTRY {country: hom.country}) WITH p, co MERGE (p) -[:IS_FROM]-> (co)', {hom: hom, p: p} ) YIELD value
RETURN value AS _, p

The first apoc.do.case clause of the query works great - I'm able to return the city/state/country node as intended and the relationships between PERSON and its residence are created correctly. However, the second apoc.do.case clause doesn't work - it doesn't seem to be passing the hom variable (containing the hometown names) so that I can use it to match the location once again and create the hometown relationship - the :IS_FROM relationships are just not created in my case.

Am I doing something wrong? I looked at this GitHub response and am not very clear on how I can supply the queries and the parameters as a list to then UNWIND within Cypher.

Any tips or advice on how to approach this would be a great help. Thanks!

1 REPLY 1

prrao
Node

I believe I got it to work! Referencing the idea from this post.

The problem is that the first apoc.do.when clause just performs a MATCH and MERGE, without returning anything. So the YIELD statement doesn't yield anything.

Adding a RETURN true at the end of each conditional cypher query does the trick, since it ensures that an empty value isn't returned at the end of the APOC call.

 WITH p, residences AS res, hometowns as hom
 CALL apoc.do.case(
   [EXISTS (res.city), 'MATCH (c:CITY {city: res.city}) -[:A_CITY_OF]-> (s:STATE {state: res.state}) -[:A_STATE_OF]-> (co:COUNTRY {country: res.country}) WITH p, c MERGE (p) -[:LIVES_IN]-> (c) RETURN true',
   NOT EXISTS (res.city) AND EXISTS (res.state), 'MATCH (s:STATE {state: res.state}) -[:A_STATE_OF]-> (co:COUNTRY {country: res.country}) WITH p, s MERGE (p) -[:LIVES_IN]-> (s) RETURN true' ],
   'MATCH (co:COUNTRY {country: res.country}) WITH p, co MERGE (p) -[:LIVES_IN]-> (co) RETURN true', {res: res, p: p} ) YIELD value
WITH value AS _, p, hom
 CALL apoc.do.case(
   [EXISTS (hom.city), 'MATCH (c:CITY {city: hom.city}) -[:A_CITY_OF]-> (s:STATE {state: hom.state}) -[:A_STATE_OF]-> (co:COUNTRY {country: hom.country}) WITH p, c MERGE (p) -[:IS_FROM]-> (c) RETURN true',
   NOT EXISTS (hom.city) AND EXISTS (hom.state), 'MATCH (s:STATE {state: hom.state}) -[:A_STATE_OF]-> (co:COUNTRY {country: hom.country}) WITH p, s MERGE (p) -[:IS_FROM]-> (s) RETURN true' ],
   'MATCH (co:COUNTRY {country: hom.country}) WITH p, co MERGE (p) -[:IS_FROM]-> (co) RETURN true', {hom: hom, p: p} ) YIELD value
RETURN value AS _, p
Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.