Having trouble grouping conditions like i would in mysql

[jmiller87]

Greetings,

I am trying to construct a query but no matter how i approach it the results aren't accurate and the performance is bad. I'll give a somewhat pseudo example of what i'm trying to achieve and hopefully someone knows how to accomplish it as i have been trying for days and searching google has not been successful.

• CODE BELOW IS SOMEWHAT PSEUDO SO I KNOW IT NOT CORRECT. i just wanted to try and make it as clear as possible what i'm trying to achieve *

MATCH (e:User {id: 163014})

MATCH (a:Application)-[:IS_AT]->(l:Location)

WHERE (
	(e)-[:HAS_ACCESS_TO]->(l)<-[:IS_AT]-(a)
        AND NOT (a)-[:IS_FOR]->(:Job)
)

OR 

(a)-[:IS_FOR]->(j)<-[:HAS_ACCESS_TO]-(e)

return DISTINCT(a)
skip 0
limit 100

The general summary is i'm trying to get all Application that either:
1: The User has access to via Location that they have access to BUT the application for that Location has no Job attached
2: The User has access to via Job that an application is for

An Application will either have a Location and Job attached, or Just Location. It was a simple query in mysql to write but the performance was trash. I feel that since its all about relations Neo4j should handle it much better. I just don't know how to do it

[andrew_bowman · Accepted solution]

So the first thing is to make sure you have an index or unique constraint on :User(id), so the initial lookup of your user node is fast.

Next, there are some aspects of the pattern currently in your WHERE that should be in your MATCH. You don't want a match of all (a:Application)-[:IS_AT]->(l:Location) patterns, then to filter them down in your WHERE clause, you want to MATCH to a pattern connected to your e user node.

If we look at the two patterns you're looking for, we can see some similarities we can take advantage of:

(e)-[:HAS_ACCESS_TO]->(l)<-[:IS_AT]-(a)

and (if we reverse the pattern to make it easier to compare):

(e)-[:HAS_ACCESS_TO]->(j)<-[:IS_FOR]-(a)

The only difference is an :IS_AT vs an :IS_FOR relationship from the node that the user has access to. If locations have only :IS_AT but not :IS_FOR, and if jobs only have :IS_FOR but not :IS_AT (and if there are no other node types that have these relationships that you don't want to traverse), then we can combine these into a single pattern:

MATCH (e:User {id: 163014})-[:HAS_ACCESS_TO]->(node)<-[:IS_AT|IS_FOR]-(a)
WHERE NOT (node:Location AND (a)-[:IS_FOR]->())

This assumes that :IS_FOR nodes from applications only point to :Job nodes, as we can take advantage of relationship degree when we omit any information about the node at the other side. If other node types are involved and allowed from :IS_FOR relationships, then you'll need to put the :Job label back into the pattern to exclude, and it won't be quite as efficient.

The full query would look like:

MATCH (e:User {id: 163014})-[:HAS_ACCESS_TO]->(node)<-[:IS_AT|IS_FOR]-(a)
WHERE NOT (node:Location AND (a)-[:IS_FOR]->())
RETURN DISTINCT a
SKIP 0
LIMIT 100

Remember to create your index or unique constraint first!

[jmiller87]

Thank you so much for your help. I will give it a shot. I really appreciate you taking the time to help and give such a descriptive answer!

[jmiller87]

worked amazing! thank you so much again!