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.

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.

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');

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);

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)

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);

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)

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);

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);

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);

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);

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);

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)

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:

• AGE VIEWER
• APACHE AGE
• APACHE AGE DOCUMENTATION
• POSTGRESQL DOCUMENTATION