Dynamic evaluation of WHERE clause after SET

[matthias_muelle]

I'm trying to set up a plain Cypher query (without additional frameworks like APOC), which does the following:

1. Filter employees depending on their workspace, skill and skill level
2. Create teams for each passed skill object until a maximum amount of percent work is reached per team
3. 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

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

[Cobra]

Hello @matthias.mueller and welcome to the Neo4j community

Could you RETURN

t.skillName, m.name, t.percent

I would like to see if t.percent is updated each time.

Regards,
Cobra

[matthias_muelle]

Hi Cobra

Thank you very much Thank you for the suggestion. I would say, that t.percent is updated at the end:

I've removed the names, as it is actual data, I hope this is fine.

Also, my approach doesn't work with Bloom, as I can't use SETs there. As I'm learning by doing, I'm generally not sure, if this approach is feasible or if there is an easier way to make this work?

[Cobra]

One solution could be to collect and to UNWIND after, normally with UNWIND, your property should be updated at each iteration

[matthias_muelle]

Thank you your input pointed me in the correct direction. I've trimmed the cypher statement to make it easier to comprehend the case. A working solution is:

MATCH 	(n:Team) DETACH DELETE n
WITH 1 	AS _
MATCH 	(n:Member) DETACH DELETE n
WITH 1 	AS _
MERGE	(t: Team { skillName: 'Java', percent: 150 })
MERGE	(:Member { name: 'Matthias Müller', percent: 90, mainSkill: 'Java' })
MERGE	(:Member { name: 'John Doe', percent: 90, mainSkill: 'Java' })
MERGE	(:Member { name: 'Jane Doe', percent: 90, mainSkill: 'Java' })
WITH 	1 AS ignore
MATCH 	(m:Member)
WITH 	m
MATCH	(t:Team { skillName: m.mainSkill })
WITH	t,
		COLLECT (m) as membersList
FOREACH	(mem in membersList |
	FOREACH (_ IN CASE WHEN t.percent > 0 THEN [1] ELSE [] END |
      MERGE (mem) -  [:IS_MEMBER] -> (t)
      SET t.percent = t.percent - mem.percent
    )
)
WITH 1 as _   
MATCH (m:Member)
WHERE NOT (m)-[:IS_MEMBER]-> ()
DELETE m
WITH 1 as _
MATCH (m:Member) - [] -> (t:Team)
RETURN m, t

I have to say, I really dislike the solution, is it is a very imperative approach. But I guess there is no other way around to enforce cypher to re-evaluate a property after settings it

[Cobra]

I'm pretty sure that with APOC, we could improve and optimize the query but it was not an option to use a PLUGIN

Another idea that you could try instead of collecting and iterating, it's to use a sub-query to update the propery.

[matthias_muelle · Accepted solution]

@Cobra Thank you again, using Sub Queries seems way better than the for-each hack. If someone is finding this thread via google, my working version is looking like this:

// Clear the objects to create
OPTIONAL MATCH (n:TeamSkill) DETACH DELETE n
WITH 1 AS _

OPTIONAL MATCH (n:TeamSkillPercent) DETACH DELETE n
WITH 1 AS _

OPTIONAL MATCH (n:Member) DETACH DELETE n
WITH 1 AS _

OPTIONAL MATCH (n:Team) DETACH DELETE n
WITH 1 AS _


UNWIND 		$skills AS skill

// Create the TeamSkills per passed skill
MERGE		(ts: TeamSkill { skillName: skill.name, neededPercent: skill.percent })

// Create the TeamSkillPercents per passed skill
MERGE		(tsp: TeamSkillPercent { skillName: skill.name, freePercent: skill.percent })

// Match the skills and skill-levels of the Employees
WITH 		ts,
			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 ASC

// Calculate the free time per employee within the passed dates
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
            
// Create Member nodes and link them to the TeamSkills. Subtract the employee free percent from the TeamSkill percent
CALL
{
	WITH 		name,
				workspace,
        		mainSkill,
            	matchingSkillCount,
            	freeHoursPercent
	
    // Match the TeamSkill depending on the Employees mainSkill
    MATCH		(ts:TeamSkill { skillName: mainSkill })
    WITH		ts,
    			name,
				workspace,
        		mainSkill,
            	matchingSkillCount,
            	freeHoursPercent
    
    // Match the TeamSkillPercent as long and check if freePercent matches the Employees free hours
	MATCH		(tsp:TeamSkillPercent { skillName: ts.skillName })
    WHERE		tsp.freePercent >= freeHoursPercent
    WITH		ts,
    			tsp,
    			name,
				workspace,
        		mainSkill,
            	matchingSkillCount,
            	freeHoursPercent
                
	// Create a Member to the TeamSkill, substract the added employee free percent from the TeamSkilLpercent
	CREATE		(m:Member { name: name, percent: freeHoursPercent, matchingSkillsCount: matchingSkillCount }) - [:PROVIDES_SKILL] -> (ts)
	SET 		tsp.freePercent = tsp.freePercent - m.percent
    RETURN		1
}

// Create a Team, holding the structure together
MERGE 		(t:Team)
WITH 		t

// Link all TeamSkills to the Team
MATCH		(ts:TeamSkill)
MERGE		(ts) - [po:IS_PART_OF] -> (t)
WITH		ts,
			t,
			po
        
// Read the structure and return it
MATCH		(m:Member) - [ps:PROVIDES_SKILL] -> (ts)
RETURN 		m,
			po,
			t,
			ts,
        	ps