Skip to content

Instantly share code, notes, and snippets.

@yaravind
Last active December 22, 2015 14:38
Show Gist options
  • Save yaravind/6486525 to your computer and use it in GitHub Desktop.
Save yaravind/6486525 to your computer and use it in GitHub Desktop.
Sports Leagues version 1.0
= Models Sports Leagues
Aravind R. Yarram <yaravind@gmail.com>
v1.0, 08-Sep-2013
== Domain Model
Each *League* has multiple *Level*s like playoffs, quarter-finals etc. The levels are ordered: first is playoffs, +NEXT+ is quarter-finals, +NEXT+ is semi-finals and then the next and last one is the finals. The ordering is represented using a http://docs.neo4j.org/chunked/milestone/cookbook-linked-list.html[linked-list].
A *Player* can play for more than one team over multiple leagues but can only play for a single team in a given league. This is captured by the +PLAYED_IN_FOR_LEAGUE+ http://docs.neo4j.org/chunked/milestone/cypher-cookbook-hyperedges.html[hyperedge] between player, team and league using http://docs.neo4j.org/chunked/milestone/cypher-cookbook-hyperedges.html[hypernode] *PlayerTeamLeague* . A team can register in a new league with a different name in which case, we want to know what it was +PREVIOUSLY_KNOWN_AS+.The fact that a player had for a given team (irrespective of which league) is captured by +PLAYED_WITH_TEAM+ between the player and team to simplify the queries.
In each level, *Teams* are organized into *Pools* and each pool consists of one or more teams. Each pool has fixtures i.e. the *Matches* played between two teams, +TEAM_A+ and +TEAM_B+ respectively. Each match is +PLAYED_ON+ a court. Each match has a +WINNER+, +LOSER+ and a +MVP+.
A team can have many players but only subset of them, allowed by the game rules (for e.g. only 2 players can play table tennis). This is captured by the +TEAM_A_PLAYER+ and +TEAM_B_PLAYER+ relationships from match to each of the players who played this match.
Each league is +PLAYED_AT+ one or more *Venues*. Each venue has one or more *Courts*. The more the courts and venues, the faster we can complete the league.
Each league gives away certain Awards. Awards are +AWARDED_TO+ teams and players. Teams win +TEAM_AWARD+ s and players win +INDIVIDUAL_AWARD+ s.
image::https://raw.github.com/funpluscharity/avleague/master/avleague/src/test/resources/sports_domain_leagues.jpg[Domain Model]
== Setup
Let us relaize this model using Volleyball as the sport. This model (with more enhancements) will eventually be used as a database for http://www.AtlantaVolleyballLeague.com[Atlanta Volleyball League]
//hide
//setup
//output
[source,cypher]
----
//Nodes for league 1
CREATE (league:League{ name:'September Volleyball League' })
CREATE (winner:Award { name:'Winner' })
CREATE (runner:Award { name:'Runner' })
CREATE (spiker:Award { name:'Best Spiker' })
//linked list
CREATE (playoffs:Level { name:'Playoffs' })
CREATE (quarters:Level { name:'Quarterfinal' })
CREATE (semis:Level { name:'Semifinal' })
CREATE (final:Level { name:'Final' })
CREATE (oceePark:Venue { name:'Ocee Park' })
CREATE (low:Court { name:'Low Court' })
CREATE (high:Court { name:'High Court' })
CREATE (poolA:Pool { name:'Pool A' })
CREATE (poolAQuarterfinal:Pool { name:'Pool A' })
CREATE (falcons:Team { name:'Falcons' })
CREATE (bob:Player { name:'Bob' })
CREATE (alice:Player { name:'Alice' })
CREATE (broncos:Team { name:'Broncos' })
CREATE (ted:Player { name:'Ted' })
CREATE (carol:Player { name:'Carol' })
CREATE (lakers:Team { name:'Lakers' })
CREATE (paul:Player { name:'Paul' })
CREATE (aravind:Player { name:'Aravind' })
CREATE (rishik:Player { name:'Rishik' })
CREATE (esha:Player { name:'Esha' })
//hyper nodes
CREATE (bobFalconsSeptemberLeague:PlayerTeamLeague { name:'Bob_Falcons_September_League' })
CREATE (aliceFalconsSeptemberLeague:PlayerTeamLeague { name:'Alice_Falcons_September_League' })
CREATE (tedBroncosSeptemberLeague:PlayerTeamLeague { name:'Ted_Broncos_September_League' })
CREATE (carolBroncosSeptemberLeague:PlayerTeamLeague { name:'Carol_Broncos_September_League' })
CREATE (match1:Match { name:'Falcons versus Broncos' })
CREATE (match2:Match { name:'Falcons versus Lakers' })
CREATE (quartersMatch1:Match { name:'Falcons versus Broncos Quarters' })
//Relationships
CREATE league-[:TEAM_AWARD]->winner
CREATE league-[:TEAM_AWARD]->runner
CREATE league-[:INDIVIDUAL_AWARD]->spiker
CREATE league-[:PLAYED_AT]->oceePark
CREATE oceePark-[:COURT]->high
CREATE oceePark-[:COURT]->low
CREATE league-[:LEVEL]->playoffs
CREATE playoffs-[:NEXT]->quarters
CREATE quarters-[:NEXT]->semis
CREATE semis-[:NEXT]->final
CREATE playoffs-[:POOL]->poolA
CREATE quarters-[:POOL]->poolAQuarterfinal
CREATE poolA-[:TEAM]->falcons
CREATE poolA-[:TEAM]->broncos
CREATE poolA-[:TEAM]->lakers
CREATE poolAQuarterfinal-[:TEAM]->falcons
CREATE poolAQuarterfinal-[:TEAM]->broncos
CREATE poolA-[:FIXTURE]->match1
CREATE poolA-[:FIXTURE]->match2
CREATE poolAQuarterfinal-[:FIXTURE]->quartersMatch1
//hyper edges
CREATE bob-[:PLAYED_FOR_IN_LEAGUE]->bobFalconsSeptemberLeague-[:PLAYED_FOR]->falcons
CREATE bobFalconsSeptemberLeague-[:IN_LEAGUE]->league
CREATE alice-[:PLAYED_FOR_IN_LEAGUE]->aliceFalconsSeptemberLeague-[:PLAYED_FOR]->falcons
CREATE aliceFalconsSeptemberLeague-[:IN_LEAGUE]->league
CREATE ted-[:PLAYED_FOR_IN_LEAGUE]->tedBroncosSeptemberLeague-[:PLAYED_FOR]->broncos
CREATE tedBroncosSeptemberLeague-[:IN_LEAGUE]->league
CREATE carol-[:PLAYED_FOR_IN_LEAGUE]->carolBroncosSeptemberLeague-[:PLAYED_FOR]->broncos
CREATE carolBroncosSeptemberLeague-[:IN_LEAGUE]->league
CREATE paul-[:PLAYED_FOR_IN_LEAGUE]->paulLakersSeptemberLeague-[:PLAYED_FOR]->lakers
CREATE paulLakersSeptemberLeague-[:IN_LEAGUE]->league
CREATE aravind-[:PLAYED_FOR_IN_LEAGUE]->aravindLakersSeptemberLeague-[:PLAYED_FOR]->lakers
CREATE aravindLakersSeptemberLeague-[:IN_LEAGUE]->league
CREATE rishik-[:PLAYED_FOR_IN_LEAGUE]->rishikLakersSeptemberLeague-[:PLAYED_FOR]->lakers
CREATE rishikLakersSeptemberLeague-[:IN_LEAGUE]->league
CREATE esha-[:PLAYED_FOR_IN_LEAGUE]->eshaLakersSeptemberLeague-[:PLAYED_FOR]->lakers
CREATE eshaLakersSeptemberLeague-[:IN_LEAGUE]->league
//to simplify queries
CREATE bob-[:PLAYED_WITH_TEAM]->falcons
CREATE alice-[:PLAYED_WITH_TEAM]->falcons
CREATE ted-[:PLAYED_WITH_TEAM]->broncos
CREATE carol-[:PLAYED_WITH_TEAM]->broncos
CREATE paul-[:PLAYED_WITH_TEAM]->lakers
CREATE aravind-[:PLAYED_WITH_TEAM]->lakers
CREATE rishik-[:PLAYED_WITH_TEAM]->lakers
CREATE esha-[:PLAYED_WITH_TEAM]->lakers
CREATE falcons-[:CONTESTED_IN]->league
CREATE broncos-[:CONTESTED_IN]->league
CREATE lakers-[:CONTESTED_IN]->league
//falcons versus broncos
CREATE match1-[:TEAM_A]->falcons
CREATE match1-[:TEAM_B]->broncos
CREATE match1-[:WINNER]->falcons
CREATE match1-[:LOSER]->broncos
CREATE match1-[:MVP]->bob
CREATE match1-[:TEAM_A_PLAYER]->bob
CREATE match1-[:TEAM_A_PLAYER]->alice
CREATE match1-[:TEAM_B_PLAYER]->ted
CREATE match1-[:TEAM_B_PLAYER]->carol
CREATE league-[:PART_OF_LEAGUE]->match1
CREATE match1-[:PLAYED_ON]->high
//falcons versus lakers
CREATE match2-[:TEAM_A]->falcons
CREATE match2-[:TEAM_B]->lakers
CREATE match2-[:WINNER]->falcons
CREATE match2-[:LOSER]->lakers
CREATE match2-[:MVP]->bob
CREATE match2-[:TEAM_A_PLAYER]->bob
CREATE match2-[:TEAM_A_PLAYER]->alice
CREATE match2-[:TEAM_B_PLAYER]->paul
CREATE match2-[:TEAM_B_PLAYER]->aravind
CREATE match2-[:TEAM_B_PLAYER]->rishik
CREATE league-[:PART_OF_LEAGUE]->match2
CREATE match2-[:PLAYED_ON]->low
//falcons versus broncos quarters
CREATE quartersMatch1-[:TEAM_A]->falcons
CREATE quartersMatch1-[:TEAM_B]->broncos
CREATE quartersMatch1-[:WINNER]->falcons
CREATE quartersMatch1-[:LOSER]->broncos
CREATE quartersMatch1-[:MVP]->alice
CREATE quartersMatch1-[:TEAM_A_PLAYER]->bob
CREATE quartersMatch1-[:TEAM_A_PLAYER]->alice
CREATE quartersMatch1-[:TEAM_B_PLAYER]->ted
CREATE quartersMatch1-[:TEAM_B_PLAYER]->carol
CREATE league-[:PART_OF_LEAGUE]->quartersMatch1
CREATE quartersMatch1-[:PLAYED_ON]->high
CREATE winner-[:AWARDED_TO]->falcons
CREATE runner-[:AWARDED_TO]->broncos
CREATE spiker-[:AWARDED_TO]->alice
//Nodes for league 2
CREATE (octLeague:League { name:'October Volleyball League' })
CREATE octLeague-[:TEAM_AWARD]->(winnerOct:Award { name:'Winner' })
//linked list
CREATE octLeague-[:LEVEL]->(playoffsOct:Level { name:'Playoffs' })
CREATE playoffsOct-[:NEXT]->(finalOct:Level { name:'Final' })
CREATE octLeague-[:PLAYED_AT]->(donPark:Venue { name:'Don White Memorial Park' })
CREATE donPark-[:COURT]->(c1:Court { name:'Court 1' })
CREATE donPark-[:COURT]->(c2:Court { name:'Court 2' })
CREATE (megaBytes:Team { name:'MegaBytes' })-[:PREVIOUSLY_KNOWN_AS]->falcons
CREATE (john:Player { name:'John' })-[:PLAYED_WITH_TEAM]->megaBytes
CREATE alice-[:PLAYED_WITH_TEAM]->megaBytes
//hyper edges
CREATE john-[:PLAYED_FOR_IN_LEAGUE]->johnMegaBytesOctoberLeague-[:PLAYED_FOR]->megaBytes
CREATE johnMegaBytesOctoberLeague-[:IN_LEAGUE]->octLeague
CREATE alice-[:PLAYED_FOR_IN_LEAGUE]->aliceMegaBytesOctoberLeague-[:PLAYED_FOR]->megaBytes
CREATE aliceMegaBytesOctoberLeague-[:IN_LEAGUE]->octLeague
//register full broncos team in october league
CREATE ted-[:PLAYED_FOR_IN_LEAGUE]->tedBroncosOctoberLeague-[:PLAYED_FOR]->broncos
CREATE tedBroncosOctoberLeague-[:IN_LEAGUE]->octLeague
CREATE carol-[:PLAYED_FOR_IN_LEAGUE]->carolBroncosOctoberLeague-[:PLAYED_FOR]->broncos
CREATE carolBroncosOctoberLeague-[:IN_LEAGUE]->octLeague
CREATE winnerOct-[:AWARDED_TO]->broncos
----
=== Try other queries yourself!
//console
== Use Cases
=== League
==== Get all leagues and venues.
[source,cypher]
----
MATCH l:League-[:PLAYED_AT]->v:Venue-[:COURT]->c:Court
RETURN l.name AS League, v.name AS Venue, collect(c.name) AS Courts
----
//table
==== Get total teams contested by league.
[source,cypher]
----
MATCH p:Player-[:PLAYED_FOR_IN_LEAGUE]->hyperEdge-[:PLAYED_FOR]->t:Team, hyperEdge-[:IN_LEAGUE]->l:League
RETURN COUNT(DISTINCT t) AS TotalTeamsContested, l.name AS League
----
//table
==== How many teams participated in a given league?
[source,cypher]
----
MATCH t:Team<-[:PLAYED_FOR]-hyperEdge-[:IN_LEAGUE]->l:League
WHERE l.name='September Volleyball League'
RETURN count(DISTINCT t) AS TotalTeamsContested
----
//table
==== Get total players participated by league.
[source,cypher]
----
MATCH p:Player-[:PLAYED_FOR_IN_LEAGUE]->hyperEdge-[:PLAYED_FOR]->t:Team, hyperEdge-[:IN_LEAGUE]->l:League
RETURN count(p) AS TotalPlayersContested, l.name AS League
----
//table
==== How many players contested in a given league?
[source,cypher]
----
MATCH p:Player-[:PLAYED_FOR_IN_LEAGUE]->hyperEdge-[:PLAYED_FOR]->t:Team, hyperEdge-[:IN_LEAGUE]->l:League
WHERE l.name='September Volleyball League'
RETURN count(p) AS TotalPlayersContested
----
//table
==== How many players contested in a given league, group by Team.
[source,cypher]
----
MATCH p:Player-[:PLAYED_FOR_IN_LEAGUE]->hyperEdge-[:PLAYED_FOR]->t:Team, hyperEdge-[:IN_LEAGUE]->l:League
WHERE l.name='September Volleyball League'
RETURN t.name AS Team, count(p) AS TotalPlayersContested, collect(p.name) AS Players
----
//table
==== Where was this league organized?
[source,cypher]
----
MATCH l:League-[:PLAYED_AT]->v:Venue-[:COURT]->c:Court
WHERE l.name='September Volleyball League'
RETURN v.name AS Venue, collect(c.name) AS Courts
----
//table
==== Get all the levels of a given league in the order they are played.
[source,cypher]
----
MATCH p=league:League-[r:LEVEL|NEXT*]->l:Level
WHERE league.name='September Volleyball League'
WITH last(nodes(p)) AS levels
RETURN COLLECT(levels.name) AS LevelsInOrder
----
//table
==== Get the levels of each league.
[source,cypher]
----
MATCH p=league:League-[r:LEVEL|NEXT*]->l:Level
WITH last(nodes(p)) AS levels, league
RETURN league.name AS League, COLLECT(levels.name) AS LevelsInOrder
----
//table
=== Team
==== Get a list of all the players that had ever played for a given team. Simplified by using the played_with_team relationship.
[source,cypher]
----
MATCH p:Player-[:PLAYED_WITH_TEAM]->t:Team WHERE t.name='Falcons'
RETURN p AS Players
----
//table
==== Get a list of players for a given team for a given league.
[source,cypher]
----
MATCH p:Player-[:PLAYED_FOR_IN_LEAGUE]->hyperEdge-[:PLAYED_FOR]->t:Team, hyperEdge-[:IN_LEAGUE]->l:League
WHERE l.name='September Volleyball League' AND t.name='Lakers'
RETURN p AS Players
----
//table
==== Get all the leagues this team had played in.
[source,cypher]
----
MATCH t:Team<-[:PLAYED_FOR]-hyperEdge-[:IN_LEAGUE]->l:League
WHERE t.name='Broncos'
RETURN DISTINCT l.name AS Leagues
----
//table
==== How many leagues this team has WON?
[source,cypher]
----
MATCH l:League-[:TEAM_AWARD]->a:Award-[:AWARDED_TO]->t:Team
WHERE t.name='Falcons'
RETURN t.name AS Team, a.name AS Award, count(t) AS TimesWon, l.name AS League
----
//table
==== Which levels did this team win through in a given league?
[source,cypher]
----
MATCH t:Team<-[:TEAM]-p:Pool<-[:POOL]-l:Level<-[:LEVEL|NEXT*]-league:League
WHERE league.name='September Volleyball League'
RETURN t.name AS Team, collect(l.name) AS Levels
----
//table
==== Get previous names or aliases.
[source,cypher]
----
MATCH t:Team-[:PREVIOUSLY_KNOWN_AS]->other
RETURN t.name AS CurrentName, other.name AS PreviousName
----
//table
=== Player
==== How many times part of winning (winner, runner etc) Team.
[source,cypher]
----
MATCH p:Player-[:PLAYED_FOR_IN_LEAGUE]->hyperEdge-[:PLAYED_FOR]->t:Team, hyperEdge-[:IN_LEAGUE]->l:League, l:League-[:TEAM_AWARD]->a:Award-[:AWARDED_TO]->t:Team
WHERE p.name='Bob' AND a.name='Winner'
RETURN count(t) AS TimesPartOfWinningTeam
----
//table
==== Get the co-players of a given player.
[source,cypher]
----
MATCH p:Player-[:PLAYED_WITH_TEAM]->t:Team<-[:PLAYED_WITH_TEAM]-coPlayer:Player
WHERE p.name='Alice'
RETURN coPlayer.name AS Coplayer, t.name AS FromTeam
----
//table
==== How many MVP awards are won by the given player?
[source,cypher]
----
MATCH p:Player-[:MVP]-m:Match
WHERE p.name='Bob'
RETURN count(p) AS TimesWonTheMVPAward
----
//table
=== Awards
==== All the awards of a given league.
[source,cypher]
----
MATCH league:League-[r:INDIVIDUAL_AWARD|TEAM_AWARD]->award
WHERE league.name='September Volleyball League'
RETURN award.name AS Award, TYPE(r) AS AwardType
----
//table
==== Winners of the awards of a given league.
[source,cypher]
----
MATCH league:League-[:INDIVIDUAL_AWARD|:TEAM_AWARD]->award:Award-[:AWARDED_TO]->awardee
WHERE league.name='September Volleyball League'
RETURN award.name AS Award, awardee.name AS WonBy
----
//table
=== GitHub Project
A complete working example of this domain can be cloned from https://github.com/funpluscharity/avleague.git[funpluscharity/avleague]. Contact yaravind@gmail.com if you want to contribute to this project or has any suggestions on enhancing the model.
@cleishm
Copy link

cleishm commented Nov 4, 2013

Hi @funpluscharity. This GraphGist no longer works with the latest Neo4j 2.0 milestone. I've created an update here: https://gist.github.com/cleishm/7304508. Could you update this gist?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment