I'm trying to set up a plain Cypher query (without additional frameworks like APOC), which does the following:
- Filter employees depending on their workspace, skill and skill level
- Create teams for each passed skill object until a maximum amount of percent work is reached per team
- Return these teams and the connections
The params I'm passing, look like this:
:param workspaceName => 'Bern'
:param skills => [{ level: 'L2', name: 'C#', percent: 150 }, { level: 'L3', name: 'Java', percent: 80 }]
:param project => [{ beginDate: date({year: 2020, month: 9, day: 1}), endDate: date({year: 2020, month: 11, day: 1}) }]
The second one is the interesting param. A user can pass n amount of such skill-objects, which define the skill needed, the minimum level an employee needs to have and the amount of work percent.
I don't want to go too much into the detail, but the current Cypher statement looks like this:
UNWIND $skills AS skill
MERGE (t: Team { skillName: skill.name, percent: skill.percent })
WITH t, skill
MATCH (e:Employee { currentStatus: 'Employed', workspaceName: $workspaceName })
- [:HAS_SKILL_LEVEL] -> (sl:SkillLevel)
- [:IS_LEVEL_OF] -> (s:Skill)
WHERE (sl.skillLevelDescription >= skill.level AND s.skillName = skill.name)
WITH e, sl, s
ORDER BY sl.skillLevelShort DESC
UNWIND $project as p
MATCH (e) - [c:HAS_CAPACITY] - (d:DateDay)
WHERE d.date > p.beginDate AND
d.date < p.endDate
WITH e.employeeName AS name,
e.workspaceName AS workspace,
COUNT(DISTINCT(s)) AS matchingSkillCount,
COLLECT(DISTINCT(s.skillName)) AS skills,
ROUND(((SUM(c.hoursFree) * 100) / SUM(c.hoursToBeWorkedWorkspace))) AS freeHoursPercent
WHERE freeHoursPercent > 0
WITH name,
workspace,
skills,
matchingSkillCount,
freeHoursPercent,
HEAD(skills) as mainSkill
ORDER BY matchingSkillCount DESC,
freeHoursPercent DESC
MATCH (t:Team { skillName: mainSkill })
WHERE t.percent > freeHoursPercent
MERGE (m:Member { name: name, percent: freeHoursPercent, matchingSkillsCount: matchingSkillCount }) - [:IS_MEMBER] -> (t)
SET t.percent = t.percent - freeHoursPercent
WITH t
MATCH (t:Team) - [] - (m:Member)
RETURN t, m
As I am a newbie in Cypher, the code is not pretty, but it works so far. I receive a team with attached members:
Unfortunately, I can't make the last requirement work. Currently, all matching Employees are added, but I would like to have only the needed amounf of members. The formula would be something like SUM(member.freeHoursPercent) <= team.percent
As you can see on my Cypher, I've tried to do that with the
WHERE t.percent > freeHoursPercent
followed by
SET t.percent = t.percent - freeHoursPercent
My naive hope was, that the SET would influence the WHERE in a way, that only as many members are added, as there are free team-percent for them. I've also tried several other possibilities, for example FOREACH with CASE WHEN, as described here Set only if condition satisfies but return the matched node - #2 by michael.hunger
Unfortunately, none of the approaches work. Also, every approach (setting temporary properties etc.), seem very imperative and not as elegant as the Cypher queries I've produced so far.
As a newbie, I'm not sure if my mindset is wrong or if I'm missing a certain function? Generally, I would need something like "take relationships until an aggregated condition on the relationships is met".
Is there something like this around in Cypher? Or would I need to redesign the query completely to make this work?
Version: Neo4j Desktop 1.3.3
API: Neo4j Desktop
Many thanks in advance
With best regards
Matthias