Cfrgtkky

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)

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

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