Building a UEFA Champions League App with Apache AGE Graph Database
Tags: apacheage, postgresq, uefa, championsleague
Published: 2023-03-21 03:01:40

**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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ncp7lc64sxcr4gf8bivz.png)

**LET'S START**
The objective of this APP is to rate the players by their goals and their teams. I'm using [Apache AGE](https://age.apache.org/age-manual/master/index.html) to do this, so if you want to recreate it, you have to install the **Apache AGE**.

![APACHE AGE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uumvp89l8o2j2z69cr8d.png)

**1 - CREATING THE GRAPH**
The first step is to create a GRAPH called 'uefa', where we will store informations about the players and relationships.
```sql
SELECT * FROM ag_catalog.create_graph('uefa');
```

**2 - CREATING THE NODES IN THE GRAPH**
```sql
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.
```sql
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**
```sql
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**

```sql
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**

```sql
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](https://github.com/apache/age-viewer). But you can run the following script in the terminal.

```sql
SELECT * from cypher('uefa', $$
        MATCH (V)-[R:TEAM_MATE]-(V2)
        RETURN V,R,V2
$$) as (V agtype, R agtype, V2 agtype);
```


![AGE Viewer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ajb33fss8z2x52l7ms0i.png)
**5 - WHICH TEAMMATE HAS THE MOST GOALS?**
To find this, you have to run the following scripts:

```sql
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](https://github.com/apache/age-viewer)
- [APACHE AGE](https://github.com/apache/age-viewer)
- [APACHE AGE DOCUMENTATION](age.apache.org/age-manual/master/intro/setup.html)
- [POSTGRESQL DOCUMENTATION](https://www.postgresql.org/docs/)