Cypher query to check locations and sublocations

[myshareit]

I have Profile node which may be linked to Location node via :LOCATED_IN relationship.
In turn, Location represents unlimited composite structure of nested Location nodes linked with :CONTAINS relationships between them.

I need to find all Profiles which linked to specified location IDs or their parent locations on unlimited levels.

I'm trying to create such Cypher query but it doesn't work as expected. This is what I have so far:

MATCH (d:Profile)-[:LOCATED_IN]-(l:Location) OPTIONAL MATCH (pl:Location)-[:CONTAINS*]->(l) WHERE any(x IN l.id WHERE x IN [100]) OR any(x IN pl.id WHERE x IN [100]) return d;

What am I doing wrong and how to fix it?

[koji]

Hi @myshareit

I created these data.

CREATE (:Profile {id:1})-[:LOCATED_IN]->(:Location {id:10})
CREATE (:Profile {id:2})-[:LOCATED_IN]->(:Location {id:20})-[:CONTAINS]->(:Location {id:21})

This is the basic data.
The result is 1.

MATCH (d:Profile)-[:LOCATED_IN|CONTAINS*..10]-(l:Location)
  WHERE l.id = 10
RETURN d

This is the data for CONTAINS Location.
The result is 2.

MATCH (d:Profile)-[:LOCATED_IN|CONTAINS*..10]-(l:Location)
  WHERE l.id = 21
RETURN d