cancel
Showing results for 
Search instead for 
Did you mean: 

Database design advice: How to avoid deep query?

deepak
Node Clone

I am working on a game with a following graph:

(User)-[:CITIZEN_OF]->(Country)<-[:STATE_OF]-(State)<-[:REGISTERED_IN]-(Business)<-[:OWNED_BY]-(Investment)<-[:REPORT_OF]-(Report)

User is given access to each resource if they are CITIZEN_OF the country which holds that resource.
For example, if users want to access a Report, they make an API call: getReport(userId, reportId) which is permitted if userId is a CITIZEN_OF country which has a transitive relation with Report.

Provided I may have maximum 10K nodes at each level, will it hurt to have a deep query like this on each API request?

MATCH (u:User {userId:'userId'})-[:CITIZEN_OF]->(c)<-[:STATE_OF]-(s)<-[:REGISTERED_IN]-(b)<-[:OWNED_BY]-(i)<-[:REPORT_OF]-(r: Report{reportId:'reportId'}), 
RETURN u,r

One alternative is to add a country property in User and Report (and all other resource nodes). This approach has 2 issues:

  1. We need 2 read queries, 1 for User and 1 for Report to match their Country property for each API request
  2. State can be moved to a different country (Each child can move to a different parent). This operation won't be as common as the first one, but if that happens, I would need to recursively modify country property of all nodes under that State.

Second alternative is to add
(User)-[:HAS_ACCESS_TO]->(Resource)
relation to all the resources like Report, Investment etc. This approach will heavily impact the performance when a new Report is created, adding all users from the country.

Third alternative is adding a new
(User)-[:CITIZEN_OF]->(Country)<-[:PART_OF]-(Resource)
relation from Report to Country. This will have issues when a State moves to a new Country. We need to recursively detach and re-attach all child nodes to the new country. Changing country property feels faster than updating all relations.

I understand the best way to find a solution is to measure all the approaches, but I am hoping for some feedback and industry practices from people who are more experienced.

1 ACCEPTED SOLUTION

One of the approaches of doing such checks is to run a shortest-path query,
if it returns something then it's all good if not then the request is denied.

Or in this particular case you can do:

MATCH (u:User {userId:'userId'})-[:CITIZEN_OF]->(c)
<-[:STATE_OF]-(s)<-[:REGISTERED_IN]-(b)<-[:OWNED_BY]-(i)<-[:REPORT_OF]-(r: Report{reportId:'reportId'})
USING JOIN on c
RETURN u,r

if you PROFILE that query you should see two index lookups from either side with an expand to country and then a node-hash-join to compare the countries.

View solution in original post

2 REPLIES 2

One of the approaches of doing such checks is to run a shortest-path query,
if it returns something then it's all good if not then the request is denied.

Or in this particular case you can do:

MATCH (u:User {userId:'userId'})-[:CITIZEN_OF]->(c)
<-[:STATE_OF]-(s)<-[:REGISTERED_IN]-(b)<-[:OWNED_BY]-(i)<-[:REPORT_OF]-(r: Report{reportId:'reportId'})
USING JOIN on c
RETURN u,r

if you PROFILE that query you should see two index lookups from either side with an expand to country and then a node-hash-join to compare the countries.

Sweet. This is exactly what I was looking for!

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.