Count related nodes that have certain property

[mikeM]

Hi Neo4j'ers,

Given Companies which have related PSCs (Persons of Significant Control). I'm trying to get an ordered list of those companies based on how many of their PSCs have a certain shareholding value.

The following query returns a random set of companies with varying number of PSCs which meet the string criteria but the size on the 'pscs' collection is always 1. I've tried count(distinct b) in the return as well and the result is the same.

The return shows
company1-[PSC1],
company1-[PSC2],
company1-[PSC3],
company1-[PSC4] etc

I don't understand why the PSCs aren't return as a matched set i.e.

company1-[PSC1,PSC2,PSC3,PSC4].

match (c:Company)<-[:IS_PSC_OF]-(b:PSC) where b.naturesOfControl contains "ownership-of-shares-25-to-50-percent"
with c,b, collect(b) as pscs
return c, pscs, size(pscs) order by size(pscs) desc

Been looking around for a few hours and it's proving difficult to find any useful hints and would really appreciate some input.

Ideally I'd filter out all companies that have 4 or less PSCs that have that shares string but I'd settle for an ordered list.

Many thanks Neo Ninjas

[Cobra · Accepted solution]

Hello @mikeM

MATCH (c:Company)<-[:IS_PSC_OF]-(b:PSC)
WHERE b.naturesOfControl CONTAINS "ownership-of-shares-25-to-50-percent"
WITH c, collect(b) AS pscs
RETURN c, pscs, size(pscs) AS len
ORDER BY len DESC

Regards,
Cobra

[mikeM]

That's fab thanks Cobra. Perfect!

Seems I wasn't too far off, just getting confused around how to use size and ordering at the end there.

Many thanks!

[Cobra]

No problem

The issue was in the WITH clause:

• you: with c,b, collect(b) as pscs
• me: WITH c, collect(b) AS pscs

Regards,
Cobra