Adapt query to return correct results

[mnathanail]

I have the bellow query. Here i collect all rows and skills, unwind rows as row and check that candidates' skills are in all skills i want, i check that every skill related to this candidate is on the list of ALL skills i want. I need to change somehow the query because i want the query to return me Candidates only if "Knows" all required skills AND for every skill has the same or more years of experience. In a nutshell, if a Candidate Knows all skills and for every skill has same or more years of experience, i want this node, otherwise no. Any ideas?

WITH  [
        {entity_id: 7, years_of_experience: 5},
        {entity_id: 23776, years_of_experience: 1 },
        {entity_id: 17477, years_of_experience: 1 }
      ]  AS rows
UNWIND rows AS row
MATCH  (s:SkillNode)
  WHERE s.entity_id = row.entity_id
WITH  collect(row) as rows, collect(s) as allSkills, s
UNWIND rows as row
MATCH (c:CandidateNode)-[r:KNOWS]->(s)
  WHERE  (r.years_of_experience>=row.years_of_experience AND s in allSkills)
WITH c, collect(distinct r) as rels, collect(distinct s) as skills, allSkills
  WHERE ALL(sk in allSkills where sk in skills)
RETURN skills, rels, c;

Example

Params (name: years_of_experience) java:3 , php:2, c#:2
Candidate 1) java:3 , php:33, c#:1 (no)
Candidate 2) java:3, php:3, c#:2 (yes)

[Cobra · Accepted solution]

Hello @mnathanail and welcome to the Neo4j community

Pretty hard to help without a little dataset but here is a try:

WITH [
    {entity_id: 7, years_of_experience: 5},
    {entity_id: 23776, years_of_experience: 1 },
    {entity_id: 17477, years_of_experience: 1 }
] AS rows
UNWIND rows AS row
MATCH (s:SkillNode {entity_id: row.entity_id})
WITH DISTINCT rows, collect(s) AS allSkills
UNWIND range(0, size(rows)-1) AS i
WITH rows[i] AS row, allSkills[i] AS s
MATCH (c:CandidateNode)-[r:KNOWS]->(s)
WHERE r.years_of_experience >= row.years_of_experience
WITH c, collect(DISTINCT r) AS rels, collect(DISTINCT s) AS skills, allSkills
WHERE ALL(sk IN allSkills WHERE sk IN skills)
RETURN c, rels, skills

Regards,
Cobra

[mnathanail]

Hello and thanks 🙂 !

Many thanks for your message!
Having a quick look, seems to be working, but i need to test it further!

Could i ask for an explanation for this part bellow?
Just want to deeply understand the logic!

Cobra:

UNWIND range(0, size(rows)-1) AS i
WITH rows[i] AS row, allSkills[i] AS s
MATCH (c:CandidateNode)-[r:KNOWS]->(s)

Many thanks and best regards,
Manos

[Cobra]

No problem, I'm happy it solved your problem

rows and allSkills have the same size and are lists so I can UNWIND them at the same time since we want to have the information at the same row. Like this I can get the s and the years_of_experience.

Regards,
Cobra

[mnathanail]

Ok, i break the query to the middle with a return to understand it.
So first we want the Skills from the db,
MATCH (s:SkillNode {entity_id: row.entity_id})
collect them into a list and then
UNWIND range(0, size(rows)-1) AS i
and for each of them we do the matching.

I think that i got it!

I will test it further, but till now i can get correct results!
In case needed, i will text you here!

Thank you for your effort and your time!

Best regards,
Manos

[Cobra]

No problem, I'm happy to help!