Filter by count of nodes related

[joseluisjordana]

I have one label called Person and another one called Dirs. They structure is as follows:

Person:
Name;Direction;
Alfred;Milan;
Maria;London;
Albert;Paris;
Marcos;Milan;
Pablo;Madrid;
Thomas;London; (etc etc)

And the table Dirs contains just the unique values for all the different Directions:
Dirs;
London;
Milan;
Madrid;
Paris; (etc etc)

I have already created a relation called lives, which links all the persons with the city to which they belong. So at the end, for example I have the city of Londo with Maria and Thomas connected.

Here is my code:
MATCH (n:Person), (b:Dirs)
WHERE n.dir = b.dir
MERGE (n)-[:lives]-(b)

However, as my label contains thousands of nodes, at the end I have some directions that have just one node linked. I would like to filter by counting all the linked nodes so at the end, for example, I get all those cities that have more than 2 persons linked.

Thanks a lot

[tony_chiboucas · Accepted solution]

Keep in mind that your MERGE will create new relationships with an arbitrary direction unless you specify a direction. Your graph design is very important here, that command should be more like:

MATCH (n:Person), (b:Dirs)
WHERE n.dir = b.dir
MERGE (n)-[:LIVES]->(b)

That only needs to be ran when you've added people or locations.

List of "biggest" Dirs

MATCH (d:Dirs)<-[:Lives]-(p:Person)
RETURN d.dir, count(p) as numResidents
ORDER BY d.numResidents DESC

More fun, a graph of the people and top 10 Dirs:

MATCH (d:Dirs)<-[:Lives]-(p:Person)
WITH d, count(p) as numResidents 
    ORDER BY numResidents DESC
    LIMIT 10
MATCH (d)<-[:Lives]-(p:Person)
RETURN d, p

[andrew_bowman]

To correct something here, a MERGE of a relationship between nodes CAN be directionless.

This will match to paths with the relationship in either direction, but if no such relationship exists, then the relationship will be created with some arbitrary direction.

CREATE on the other hand MUST have a direction for all relationships in the pattern.

[joseluisjordana]

Waoooo Tony, thanks a lot for this post. You solved my doubt.

Beisdes, I were also able to filter without the ordering. I just made a simple modification to your code and now works more than perfect. I post it here:

MATCH (d:Dirs)<-[:LIVES]-(p:Person)
WITH d, count(p) as numResidents
WHERE numResidents >=2
MATCH (d)<-[:LIVES]-(p:Person)
RETURN d, p

You are a master man!
Once again, thanks a lot!!!

[tony_chiboucas]

Glad to help Jose!

You think this is cool? The more you learn, the cooler graphs get.