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, 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:
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:
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:
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:
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:
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:
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 and GitHub repository.
Happy building!
Top comments (0)