DEV Community

Cover image for Building a UEFA Champions League App with Apache AGE Graph Database
Marcos Silva
Marcos Silva

Posted on

Building a UEFA Champions League App with Apache AGE Graph Database

INTRODUCTION
A graph database is a type of database that uses graphs to store, map and query data. In a graph database, data is represented as nodes and edges, which represent the relationships between nodes. The nodes and edges can have properties that describe additional details about them.

Graph

LET'S START
The objective of this APP is to rate the players by their goals and their teams. I'm using Apache AGE to do this, so if you want to recreate it, you have to install the Apache AGE.

APACHE AGE

1 - CREATING THE GRAPH
The first step is to create a GRAPH called 'uefa', where we will store informations about the players and relationships.

SELECT * FROM ag_catalog.create_graph('uefa');
Enter fullscreen mode Exit fullscreen mode

2 - CREATING THE NODES IN THE GRAPH

SELECT * FROM cypher('uefa', $$ CREATE 
   (:Player { name: 'Benzema', team: 'real madrid', goals: 32 }), 
   (:Player { name: 'Rodrygo', team: 'real madrid', goals: 22 }),
   (:Player { name: 'Gavi', team: 'Barcelona', goals: 12 }),
   (:Player { name: 'Lewandowski', team: 'Barcelona', goals: 0 }),
   (:Player { name: 'Salah', team: 'Liverpool', goals: 31 }),
   (:Player { name: 'Firmino', team: 'Liverpool', goals: 10 })
$$) as (Player agtype);
Enter fullscreen mode Exit fullscreen mode

Tips: You can see all the created vertices running this SQL script:
SELECT * FROM cypher('uefa', $$ MATCH (v) RETURN v $$) as (v agtype);
You will see something like this:

 {"id": 844424930131969, "label": "Player", "properties": {"name": "Benzema", "team": "real madrid", "goals": 32}}::vertex
 {"id": 844424930131970, "label": "Player", "properties": {"name": "Rodrygo", "team": "real madrid", "goals": 22}}::vertex
 {"id": 844424930131971, "label": "Player", "properties": {"name": "Gavi", "team": "Barcelona", "goals": 12}}::vertex
 {"id": 844424930131972, "label": "Player", "properties": {"name": "Lewandowski", "team": "Barcelona", "goals": 0}}::vertex
 {"id": 844424930131973, "label": "Player", "properties": {"name": "Salah", "team": "Liverpool", "goals": 31}}::vertex
 {"id": 844424930131974, "label": "Player", "properties": {"name": "Firmino", "team": "Liverpool", "goals": 10}}::vertex
(6 rows)
Enter fullscreen mode Exit fullscreen mode

To close you have to press q on the keyboard.
And to see the tables you have to run this.

SELECT * FROM cypher('uefa', $$ 
    MATCH (v) 
    RETURN v.name, v.team, v.goals 
    $$) as (name agtype, team agtype, goals agtype);
Enter fullscreen mode Exit fullscreen mode

The return:

     name      |     team      | goals
---------------+---------------+-------
 "Benzema"     | "real madrid" | 32
 "Rodrygo"     | "real madrid" | 22
 "Gavi"        | "Barcelona"   | 12
 "Lewandowski" | "Barcelona"   | 0
 "Salah"       | "Liverpool"   | 31
 "Firmino"     | "Liverpool"   | 10
(6 rows)
Enter fullscreen mode Exit fullscreen mode

3 - MAKING THE RELATIONSHIPS
In this case, the relationship will be TEAM_MATE, in this moment we only want to compare two players of the same team.
Real Madrid

SELECT * FROM cypher('uefa', $$ 
    MATCH (a: Player), (b: Player) WHERE a.name = 'Benzema' 
    AND b.name = 'Rodrygo' 
    CREATE (a)-[e:TEAM_MATE { team: 'Real Madrid' }]->(b) RETURN e 
$$) as (relationship agtype);
Enter fullscreen mode Exit fullscreen mode

Barcelona

SELECT * FROM cypher('uefa', $$ 
    MATCH (a: Player), (b: Player) 
    WHERE a.name = 'Lewandowski' 
    AND b.name = 'Gavi' 
    CREATE (a)-[e:TEAM_MATE { team: 'Barcelona' }]->(b) RETURN e
$$) as (relationship agtype);
Enter fullscreen mode Exit fullscreen mode

Liverpool

SELECT * FROM cypher('uefa', $$ 
    MATCH (a: Player), (b: Player) WHERE a.name = 'Firmino' 
    AND b.name = 'Salah' 
    CREATE (a)-[e:TEAM_MATE { team: 'Liverpool' }]->(b) RETURN e 
$$) as (relationship agtype);
Enter fullscreen mode Exit fullscreen mode

4 - VISUALIZING
In this step, let's view the node relationships by AGE VIEWER. But you can run the following script in the terminal.

SELECT * from cypher('uefa', $$
        MATCH (V)-[R:TEAM_MATE]-(V2)
        RETURN V,R,V2
$$) as (V agtype, R agtype, V2 agtype);
Enter fullscreen mode Exit fullscreen mode

AGE Viewer
5 - WHICH TEAMMATE HAS THE MOST GOALS?
To find this, you have to run the following scripts:

SELECT * from cypher('uefa', $$
        MATCH (a)-[:TEAM_MATE]-(b)
        WHERE a.goals > b.goals
        RETURN a.name, a.goals, b.name, b.goals
$$) as (Player1_name agtype, Player1_goals agtype, 
Player2_name agtype, Player2_goals agtype);
Enter fullscreen mode Exit fullscreen mode

In terminal you will see something like this:

player1_name | player1_goals | player2_name  | player2_goals
--------------+---------------+---------------+---------------
 "Benzema"    | 32            | "Rodrygo"     | 22
 "Gavi"       | 12            | "Lewandowski" | 0
 "Salah"      | 31            | "Firmino"     | 10
(3 rows)
Enter fullscreen mode Exit fullscreen mode

CONCLUSION
Graph databases are a powerful way to store and organize complex data, such as that related to football. By modeling players, teams, matches, and other elements of football as nodes and edges in a graph. This allows us to answer complex questions about player and team performance, such as who has scored the most goals, which team has the best defense, or which players are most likely to provide assists, this is very usefull for any tipe of data storing.

REFERENCES:

Top comments (0)