Finding nodes that have all common intermediaries

Question

I'm creating a system in which we match orders to staff. Conceptually, an order is a request for person to do some work, and a staff is a person who can do that work. An order can have one or more requirements (i.e. restrictions on who can do the work), and a staff can have one more more requirements (i.e. qualifications to do work).

I'm attempting to create a cypher query that will give me all staff that have all of the requirements listed by a given order. Said another way, I'm trying to find all staff nodes that are related to every requirement node that is related to a given order node. My question is: how do I create a cypher query to model that business logic?

As an example, consider the following sample data:

• A picture view of my sample data
• Alternatively, here is an interactive console for my data

Look at the orderId: 1 node. it has a requires relationship to two nodes, labeled RN and ER IV. In order words, order #1 requires any applicants to have the RN qualification and the ER IV qualification. It so happens that staff member Evan (staffId: 1) has both of those qualifications, so he should be able to apply for that job. The staff member Tim has ONE of those requirements, but not both, so he should not be able to apply for that job. Additionally, orderId: 2 only has one requirement, which Evan and Tim both have, so they should both be able to apply for that job.

So in essence, if I were to start with order #1 I would want to get back only Evan. If I were to start with order #2 I would want to get back Evan and Tim*.

The following query is half way there. It will give me all unique paths from a given order to a staff member one requirement at a time. However, it does not check that EVERY requirement path is satisfied (which means currently it will only work for orders that only have a single requirement):

start o=node(2) 
match o-[:requires]->req<-[:hasRequirement]-s 
return o, req, s;

So what are my options? Can I somehow check the presence of an unknown number of matching relationships? Or will I need to model my data a different way?

*Edit: I made a mistake when setting up my sample data. Tim should have been associated with RN so that he qualified for order #2.

Answer 1

I came up with

start o=node(2) 
match o-[orderReqRel:requires]->r 
with count(orderReqRel) as orderReqs, o 
match p-[personReqRel:hasRequirement]->r<-[:requires]-o 
with count(personReqRel) as personReqs,p,orderReqs 
where personReqs=orderReqs
return p;

Seems to work for Order 1 where I get back only Evan. Order 2 doesn't seem to have a requirement that Tim has (your explanation indicates that but for some reason, not seeing it in the console you shared)

Answer 2

I guess this cypher statement solves your problem:

start o=node(2) 
match o-[:requires]->req<-[:hasRequirement]-p 
with o, p, count(req) as c 
where length(o-[:requires]-()) = c 
return p, c