Neo4J query for most common relationship

Question

I'm studying informatics and I'm currently working on a project. I had to choose one NOSQL data structure and study it myself. I chose Graph databases and did a little project to show my skills. I had to build a database in Neo4J based on a relational database.

The project focusses on bikes that you can use and put back at different stations around the city of Antwerp. Something most of the big cities have. There are 3 types of nodes in my database. First of all, there are rides. A ride consists of a rideId, a startTime, an endtime, a startLockId, and an endLockId. Then there are stations where you can put the bikes. Every ride has a startlock and an endlock. So there are also 2 types of relations between these nodes. One where the ride leaves, and one where it arrives. A station consists of a lockId, a street and a number (the housenumber). Each station is situated in a certain district in Antwerp. the districts consist of just a name. There are a total of 7 districts in the database.

There are certain queries I had to write to show my skills in Neo4J, but there is one that I just can't find. The question asks: "What districts have the most rides between them?".

Currently I came to this query which lists up the beginning and end district of all rides:

MATCH (d1:District)<--(s1:Station)<--(r:Ride)-->(s2:Station)-->(d2:District) 
WHERE d1.Name <> d2.Name 
RETURN d1, d2

I've been searching the internet, and I've been trying with COUNT(), ORDER BY() and COLLECT() methodes, but can't seem to find the solution, or come closer to it. Does anyone have an idea how I could calculate the most common relation between the districts?

user_92359 · Answer

Your query looks like it's almost there, the pattern you're looking for is correct.

You'll want a better predicate to ensure you don't get mirrored results (results for the same pairings of districts, just switching which one is bound to d1 and which is bound to d2). You can do this by adding a restriction on the graph ids of the districts: WHERE id(d1) < id(d2). This will also avoid paths back to the same district.

You'll want to use count() for the number of rides between, and then order your results, then get however many results you're interested in.

MATCH (d1:District)<--(:Station)<--(r:Ride)-->(:Station)-->(d2:District) 
WHERE id(d1) < id(d2) 
RETURN d1, d2, count(r) as rideCount
ORDER BY rideCount DESC
LIMIT 5

Neo4J query for most common relationship

1 Answers