Analyzing LEGO Sets Using a Graph Database
Tags: apacheage, postgres, database, tutorial
Published: 2023-06-20 15:31:18

Have you ever wondered which pieces you need to complete a LEGO set you're working on? As LEGO enthusiasts, we understand the excitement of building and collecting these iconic toy sets. In this blog post, we'll explore how a graph database can help us analyze LEGO sets and determine the missing pieces needed to complete them. We'll walk through the process step by step using an example LEGO set and queries in SQL, with the use of [Apache AGE](https://age.apache.org/), a graph extension for PostgreSQL.

## Understanding Graph Databases

Before we dive into analyzing LEGO sets, let's briefly explore graph databases and their benefits. Unlike traditional relational databases, graph databases excel at representing complex relationships between data points. In the context of LEGO sets, where pieces have various connections, dependencies, and quantities, a graph database provides a natural and efficient way to model these relationships.

`Pieces`, `Products`, and `Users` can be represented by vertices on our graph. A `Product` is made by various `Pieces` and the `Users` own some of these pieces. Therefore, we can simplify these relations as follows:
```
(Product)-[REQUIRES {qty: int}]->(Piece {number: int})<-[OWN {qty: int}]-(User)
```

## Creating the LEGO Graph

To get started, we need to set up our graph database to represent LEGO sets. Let's use the provided query to create our LEGO graph:

```sql
SELECT * FROM ag_catalog.create_graph('LegoGraph');
```

This query creates a graph named 'LegoGraph' that will serve as the foundation for our LEGO set analysis.

## Defining the LEGO Set

Now that we have our LEGO graph, let's define the specific set we want to analyze. We can create the LEGO set using the following query:

```sql
SELECT * FROM cypher('LegoGraph', $$
CREATE (v:Product {theme: 'Lego City', name: 'Police Patrol Car', number: 60239})
RETURN v.name, v.theme, v.number
$$) AS (name agtype, theme agtype, number agtype);
```

In this query, we create a `Product` vertex representing the LEGO City Police Patrol Car set and assign it relevant properties such as theme, name, and number.

## Specifying Required Pieces

Every LEGO set consists of various pieces, each with a specific quantity. We need to specify the required pieces for our LEGO set and their quantities. Let's add the required pieces for the Police Patrol Car set using the provided query:

```sql
SELECT * FROM cypher('LegoGraph', $$ MATCH (v:Product {number: 60239}) CREATE (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 4504369}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6213880}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6213881}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4179874}), 
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4179875}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 4547489}), (v)-[:REQUIRES {qty: 6}]->(:Piece {number: 302301}), 
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6168612}), (v)-[:REQUIRES {qty: 4}]->(:Piece {number: 4259940}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 243101}),
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6023806}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 4515359}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6031947}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 4560929}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4646574}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 416201}),
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6259271}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4159739}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4569056}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 302121}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 242023}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 301023}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 366623}), (v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6188643}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6016165}),
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6112622}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 243224}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 9553}), 
(v)-[:REQUIRES {qty: 1}]->(:Piece {number: 6172536}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 241226}), (v)-[:REQUIRES {qty: 4}]->(:Piece {number: 6029208}),
(v)-[:REQUIRES {qty: 2}]->(:Piece {number: 6199908}), (v)-[:REQUIRES {qty: 1}]->(:Piece {number: 4285883})
$$) AS (a agtype);

```
This query creates the relationships between the Police Patrol Car set and the individual pieces it requires. Each relationship has a `REQUIRES` type and specifies the quantity of the required piece.

## Creating a User

Now, let's create a user who owns some LEGO pieces. For our example, we'll create a user named Bob. Execute the following query to create the user:

```sql
SELECT * FROM cypher('LegoGraph', $$                                                                                               
CREATE (u:User {name: 'Bob'}) RETURN u.name                                                                                                              
$$) AS (name agtype);  

```

## Adding Owned Pieces

To determine the missing pieces for the user, we first need to add the pieces they already own. Let's assume Bob owns a few pieces from the Police Patrol Car set. Execute the following queries to add the owned pieces:

```sql
SELECT * FROM cypher('LegoGraph', $$
MATCH (u:User {name: 'Bob'}), (p:Piece {number: 4502089}) 
CREATE (u)-[:OWNS {qty: 4}]->(p)
$$) AS (a agtype);

SELECT * FROM cypher('LegoGraph', $$
MATCH (u:User {name: 'Bob'}), (p:Piece {number: 242023}) 
CREATE (u)-[:OWNS {qty: 2}]->(p)
$$) AS (a agtype);

SELECT * FROM cypher('LegoGraph', $$
MATCH (u:User {name: 'Bob'}), (p:Piece {number: 302301}) 
CREATE (u)-[:OWNS {qty: 2}]->(p)
$$) AS (a agtype);

```

In these queries, we match the user 'Bob' and the specific pieces he owns, and create `OWNS` relationships between them. 

## Analyzing Missing Pieces

Finally, let's determine the pieces that Bob is missing to complete the Police Patrol Car set. Execute the following query:

```sql
SELECT *
FROM cypher('LegoGraph', $$
    MATCH (v:Product {name: 'Police Patrol Car'})-[r:REQUIRES]->(p:Piece)
    OPTIONAL MATCH (p)<-[o:OWNS]-(u:User {name: 'Bob'})
    RETURN p.number,
        CASE WHEN o.qty IS NOT NULL THEN
            r.qty - o.qty
        ELSE
            r.qty
        END AS qty_missing
$$) AS (piece agtype, qty_missing agtype);

```

This query matches the Police Patrol Car set and its required pieces. It also performs an optional match with the owned pieces of Bob. By subtracting the owned quantity from the required quantity, we calculate the missing quantity for each piece. The result includes the piece number and the quantity missing. 

```
  piece  | qty_missing
---------+-------------
 242023  | 0
 302301  | 4
 4504369 | 2
 6213880 | 2
 6213881 | 2
 4179874 | 1
 4179875 | 1
 4547489 | 2
 6168612 | 2
 4259940 | 4
 243101  | 1
 6023806 | 1
 4515359 | 2
 6031947 | 1
 4560929 | 2
 4646574 | 1
 416201  | 2
 6259271 | 1
 4159739 | 1
 4569056 | 1
 302121  | 2
 301023  | 2
 366623  | 2
 6188643 | 2
 6016165 | 1
 6112622 | 1
 243224  | 1
 9553    | 1
 6172536 | 1
 241226  | 1
 6029208 | 4
 6199908 | 2
 4285883 | 1
(33 rows)
```

## Conclusion

By leveraging a graph database, we can efficiently analyze LEGO sets and determine the missing pieces needed to complete them. In this blog post, we explored the process step by step, from creating the LEGO graph to analyzing missing pieces for a specific user. Graph databases offer a flexible and intuitive way to represent complex relationships, making them a powerful tool for LEGO enthusiasts and collectors.

Also, if you want to learn more about Apache AGE, checkout their [website](age.apache.org) and [GitHub repository](https://github.com/apache/age).

Happy building!

![lego bricks](https://media.giphy.com/media/Cwf290yH98VbpeRqe0/giphy.gif)