Efficiently match combination of node IDs

[nikolai]

Happy new year all! I have tried to find a solution to the following question without success:

1. Say I have a node called Reminder which is associated with Person (r:Reminder)-[:WITH]->(p:Person)
2. I have an array of IDs, matching Persons: [1,2,3]

I want to see if a reminder exists for:

1. Each individual ID. For example, match a reminder where (p:Person {id: 1}), match a reminder where (p:Person {id: 2}), match a reminder where (p:Person {id: 3})
2. Any permutation: match a reminder where (p:Person {id: 1}) and where (p:Person {id: 2}) or match a reminder where (p:Person {id: 2}) and where (p:Person {id: 3}) or match a reminder where (p:Person {id: 1}) and where (p:Person {id: 3}) or match a reminder where (p:Person {id: 1}) and where (p:Person {id: 2}) and where (p:Person {id: 3})

I do have a query which returns the reminder which belongs to the IDs [1,2,3] but I am not sure how to also efficiently match the other permutations in the same query:

MATCH (p:Person) 
WHERE p.id in [1,2,3]
WITH collect(p) as pnodes, count(p) as count, size([1,2,3]) as idCount
MATCH (r:Reminder)-[:WITH]->(p)
WHERE ALL (pt IN pnodes WHERE (r)-[:WITH]->(pt)) 
AND count = idCount
return distinct r

This returns any reminder which has exactly the three relationships. It doesn't return a reminder if any subset of the IDs also match. Is this possible?

All the best,

Nikolai

[stefan_armbrust · Accepted solution]

How about:

MATCH (r:Reminder)-[:WITH]->(p:Person)
WHERE p.id in [1,2,3]
RETURN r.id, collect(p.id), count(*) as numberOfPeople

For permutations with 2 people involved, filter that for numberOfPeople=2.

[nikolai]

Hi Stefan, you are awesome! (And I have to say, cypher as well ) .

I modified the query a bit to only include Reminders which match a at least the IDs provided and also more (being ignored):

MATCH (r:Reminder)-[:WITH]->(p:Person)
WHERE p.id in ['1', '2', '3', '4']
WITH r.id as reminderId, collect(p.id) as peopleIds, count(*) as numberOfMatches
MATCH (q:Reminder)-[:WITH]->(:Person)
WHERE reminderId = q.id
WITH count(q) as reminderPeople, reminderId, peopleIds, numberOfMatches
WHERE numberOfMatches >= reminderPeople
return reminderId, peopleIds, numberOfMatches, reminderPeople

So in this case if a reminder exists for people [1,2,3] and another one for [4] or another one for [2,4] it will match.

Incredible how you can simply do complex queries