Query all related nodes and set property on original entity

[mikeM]

I could use some advice on how to do a specific search. I apologise if this is a ridiculous easy thing to bother you with.

Given a database with 2 different node types, Company and PSC, where Company has a one to many relationship with PSC nodes :IS_PSC_OF. I need to match all companies whose related PSCs don't come from the UK.

I can't grasp in my inexperienced Cypher abilities how to do this.

If I were coding this it would look something like this, but boy does it not like it and I'm struggling to think of an alternative after scouring the documentation.

// Find all Companies
MATCH (c:Company) 
// Loop over each 'c' counting related PSC nodes that are in the UK and set result as property on company
FOREACH (comp IN nodes(c) | SET comp.NumberUKPeople = count(MATCH (comp)-[:IS_PSC_OF]-(p) WHERE NOT p.country <> "UK"))

Then in a separate query I could get the results by simply searching for

MATCH (c:Company)
WHERE c.NumberUKPeople = 0
RETURN c

Am I over complicating this?
How can I aggregate all Companies whose related PSC's country property do not match a certain value and return all these companies?

Many thanks

[Cobra · Accepted solution]

Hello @mikeM

Something like this should do the job?

MATCH (c:Company)-[:IS_PSC_OF]-(p:PSC)
WHERE NOT p.country <> "UK"
SET c.NumberUKPeople = count(DISTINCT p)

Regards,
Cobra

[mikeM]

Wow, thanks for the quick response.

That was pretty much spot on. Cheers

I had to break down the SET as it doesn't like count being after it. So I added a WITH,

MATCH (c:Company)-[:IS_PSC_OF]-(p:PSC)
WHERE p.country <> "UK"
WITH c, count(DISTINCT p) as total
SET c.NumberUKPeople = total

I also then realised that count doesn't add a zero to the properties, so I seeded all companies with a default value of 0 and run it again so all companies which matched and had a different value would reflect it properly.

That's helped me a lot Cobra, thank you again!