CODEWITHSUNDEEP
neo4jcypher
Aligator
Aligator

Reputation: 111

Neo4j Cypher Sorting and adding results of a list with multiple duplicates

So i've got this graph database which has 20 football teams in nodes called "Team", with the only attribute being their name and a constraint ensuring uniqueness. Ive also got over 350 football matches again in nodes called "Match", with the attributes such as the date, team 1 score, team 2 score. The matches nodes have are linked in such a way that team 1 is the home_team and team 2 is the away_team so the relationship is like this:

MERGE (m)-[:away_team]->(t1)
MERGE (m)-[:away_team]->(t2)

So far all of what I mentioned has worked, however now I am trying to display the team with the most wins in January. I managed to get a return with the teams in each match, and their result. It looks like this (the code is below the image)

enter image description here

MATCH (m:Match)-[:home_team]->(home_team:Team), (m:Match)-[:away_team]->(away_team:Team)
WHERE m.date CONTAINS 'Jan'
WITH m as matches, [home_team, away_team] as teams
return teams, collect (matches.home_score) AS home_result, collect (matches.away_score) AS away_result

After getting to this point I got stuck. Im not entirely sure where go from here. The issue is since one team will play multiple matches in January and they can be either home or away so their result is spread between two columns and Im not entirely sure how to extract it and associate the team result to the team name and then display the highest score from the won matches. Any help is greatly appreciated!

P.S. Im new to Cypher/Neo4j

Upvotes: 0

Views: 287

Answers (1)

InverseFalcon
InverseFalcon

Reputation: 30397

Something like this might work, using both relationship types in the MATCH (it will match to both types), then using some boolean logic to filter out the uninteresting results and getting the top team by count:

MATCH (t:Team)<-[r:home_team|away_team]-(m:Match)
WHERE m.date CONTAINS 'Jan'
WITH t, type(r) = 'home_team' as isHome, m.home_score > m.away_score as homeWin
WHERE (isHome AND homeWin) OR (NOT isHome AND NOT homeWin)
RETURN t, count(*) as matchesWon
ORDER BY matchesWon DESC
LIMIT 1

This could be a bit easier if you refactored your graph to add won as a boolean property of the :home_team and :away_team relationships (maybe tie as well, but that's outside of the scope of this query).

If such a property existed (and was either updated with data import or when scores were updated), then your query here would become simpler:

MATCH (t:Team)<-[:home_team|away_team {won:true}]-(m:Match)
WHERE m.date CONTAINS 'Jan'
RETURN t, count(*) as matchesWon
ORDER BY matchesWon DESC
LIMIT 1

Upvotes: 1

Related Questions