CODEWITHSUNDEEP
databaseneo4jcypher
Peloponeso31
Peloponeso31

Reputation: 25

(n)->(on)<-(n) cypher query gives me a cartesian product

I have a Cypher query that goes something like this:

MATCH (t1:Team)-[:PLAYS_ON]->(m:Match)<-[:PLAYS_ON]-(t2:Team)
RETURN t1 AS "Team 1", m AS "Match", t2 as "Team 2"

My goal is to have a query that allows me to see matches and which teams are matching eachother in a deportive context.

Assuming Team 1, Team 2 and Team 3, and the matches being Team 1 vs Team 2 and Team 2 vs Team 3, my expected output is:

+------+-----+------+
|Team 1|match|Team 2|
|------+-----+------|
|Team 1|date |Team 2|
|Team 2|date |Team 3|
+------+-----+------+

But I get:

+------+-----+------+
|Team 1|match|Team 2|
|------+-----+------|
|Team 1|date |Team 2|
|Team 2|date |Team 1|
|Team 2|date |Team 3|
|Team 3|date |Team 2|
+------+-----+------+

I'm relatively new to Cypher/Neo4J, so, I would not be impressed if it turns out i'm commiting a very obvious and stupid mistake, but I don't have the brains to see it.

Thank you for your answers!

Upvotes: 0

Views: 37

Answers (2)

Marj
Marj

Reputation: 495

An alternative way to achieve what you want is this query (using Nimrod Serok's graph):

MATCH (t1:Team)-[:PLAYS_ON]->(m:Match)<-[:PLAYS_ON]-(t2:Team)
WHERE id(t1) < id(t2)
RETURN t1, m, t2

This works because the WHERE clause ensures that the same team pairing doesn't get considered in both directions (which causes the cartesian product).

Upvotes: 0

nimrod serok
nimrod serok

Reputation: 16033

One way to do it is:

MATCH (t:Team)-[:PLAYS_ON]->(m:Match)
WITH collect(t) AS t, m
RETURN t[0] AS t1, m, t[1] AS t2

Which on this sample data:

MERGE (a:Team{name: 'Team1'})
MERGE (b:Team{name: 'Team2'})
MERGE (c:Team{name: 'Team3'})
MERGE (d:Match{Date: '2022-05-11'})
MERGE (e:Match{Date: '2022-05-12'})

MERGE (a)-[:PLAYS_ON]-(d)
MERGE (b)-[:PLAYS_ON]-(d)
MERGE (b)-[:PLAYS_ON]-(e)
MERGE (c)-[:PLAYS_ON]-(e)

Will give you this:

╒════════════════╤═════════════════════╤════════════════╕
│"t1"            │"m"                  │"t2"            │
╞════════════════╪═════════════════════╪════════════════╡
│{"name":"Team1"}│{"Date":"2022-05-11"}│{"name":"Team2"}│
├────────────────┼─────────────────────┼────────────────┤
│{"name":"Team2"}│{"Date":"2022-05-12"}│{"name":"Team3"}│
└────────────────┴─────────────────────┴────────────────┘

In order to understand this solution, you can read about the concept of cardinality.

Basically, since the number of options for the first MATCH is two per each (:Match) (two teams, one match) the query will return two options per each (:Match). Since you want only one results per match, you can use collect to group these two lines into one.

In other words, your query is saying get all options for this arrangement, meaning two per match. The query here is getting all options per match and then "group by" match, to create a list of teams per each match.

Upvotes: 1

Related Questions