3

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:

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.

ean5533
  • 8,884
  • 3
  • 40
  • 64

2 Answers2

6

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
Stefan Armbruster
  • 39,465
  • 6
  • 87
  • 97
3

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)

Luanne
  • 19,145
  • 1
  • 39
  • 51
  • Good catch, I made a mistake when setting up my sample data. In any case, your answer works perfectly, as does Stefan's -- I wish I could accept both of your answers. I'm going to pick Stefan's answer just because it's a bit more concise, but thank you for answering as well. – ean5533 Feb 02 '13 at 21:11
  • 1
    Stefan's is much better :-) – Luanne Feb 04 '13 at 03:33