How to return for every node all neighbour nodes sorted by relation

Question

I hope you can help me. I want to count for every node all its neighbours sperated by the type of relationship. For example if i got this graph:

I want to get for Node 165 following:

id     AnzTaxi    AnzBus   AnzSchiff
165    2          2        0

I made this query, but it seems like neo4j connects my "Match" as an AND so it will only list nodes, which got at least 1 relationship at every type.

MATCH (Station)-[:TAXI]-(b) 
MATCH (Station)-[:BUS]-(c) 
MATCH (Station)-[:SCHIFF]-(d)
RETURN Station.id, COUNT(DISTINCT b) AS AnzTaxi, 
COUNT(DISTINCT c) AS AnzBus, COUNT(DISTINCT d) AS 
AnzSchiff 
ORDER BY COUNT(DISTINCT b) DESC;

Many thanks in advance!

Answer 1

You should use a OPTIONAL MATCH instead of a simple MATCH. The docs says:

The OPTIONAL MATCH clause is used to search for the pattern described in it, while using nulls for missing parts of the pattern.

Try it:

OPTIONAL MATCH (Station)-[:TAXI]-(b) 
OPTIONAL MATCH (Station)-[:BUS]-(c) 
OPTIONAL MATCH (Station)-[:SCHIFF]-(d)
RETURN Station.id, COUNT(DISTINCT b) AS AnzTaxi, 
COUNT(DISTINCT c) AS AnzBus, COUNT(DISTINCT d) AS 
AnzSchiff 
ORDER BY COUNT(DISTINCT b) DESC;

Answer 2

One additional approach is not to expand at all, and use relationship degrees (which are stored on the node itself) to get the counts you need.

MATCH (Station)
RETURN Station.id, 
 size((Station)<-[:BUS]-()) AS AnzBus, 
 size((Station)<-[:TAXI]-()) AS AnzTaxi, 
 size((Station)<-[:SCHIFF]-()) AS AnzSchiff 
ORDER BY AnzBus DESC;

Note that this counts the relationships rather than nodes, and this assumes (from your example) that every :BUS, :TAXI, and :SCHIFF relationship in the graph has both incoming and outgoing relationships between each connected node.

Though if that is the case, it's better to only model this with one relationship between nodes and treat it as bidirectional rather than double your relationships unnecessarily (and if you do make that change you'll need to remove direction from the relationships in my query).

If your model doesn't work like this, and a relationship can go one way, but not be reciprocated (so there can be an outgoing :BUS relationship to a node, but no incoming :BUS relationships from that same node), then this answer won't work, and you should choose one of the others.

Answer 3

An alternate approach would be to match all of the neighbour nodes in one go rather than three separate optional statements. This way if there was no result then you would know there were no neighbours connected by TAXI, BUS, or SCHIFF. Then you could just use CASE statements to separate them after the fact and aggregate them back up using SUM.

MATCH (s1:Station)-[mode:TAXI|BUS|SCHIFF]-(neighbour)
WITH s1, 
     TYPE(mode) as mode, 
     COLLECT(DISTINCT neighbour) as neighbours
WITH s1,
     CASE WHEN mode = 'TAXI' THEN size(neighbours) END AS AnzTaxi,
     CASE WHEN mode = 'BUS' THEN size(neighbours) END AS AnzBus,
     CASE WHEN mode = 'SCHIFF' THEN size(neighbours) END AS AnzSchiff
RETURN s1.id, 
     SUM(AnzTaxi) as AnzTaxi, 
     SUM(AnzBus) AS AnzBus, 
     SUM(AnzSchiff) AS AnzSchiff
ORDER BY AnzTaxi DESC, s1.id