I have created a graph to store LEGO products, the pieces of which each product requires, and the pieces that users already have. There are three labels for the vertices: Piece, Product, and User, and there are two labels for the edges: REQUIRES and OWNS. These vertices are connected in the following way:
(Product)-[REQUIRES {qty: int}]->(Piece {number: int})<-[OWN {qty: int}]-(User)
As an example, here's the current graph that I'm creating:
-- Creating the graph.
SELECT * FROM ag_catalog.create_graph('LegoGraph');
-- Creating the product.
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);
-- Specifying which pieces are required to build this product and their quantities.
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);
-- Creating an user.
SELECT * FROM cypher('LegoGraph', $$
CREATE (u:User {name: 'Bob'}) RETURN u.name
$$) AS (name agtype);
-- The pieces that he owns.
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);
What is the proper way to return the pieces (and their quantities) of a LEGO set that the user does not have yet, so that he/she can build it?
You can simply MATCH for the pieces required in a specified product and then use OPTIONAL MATCH for the desired user to check, returning the piece IDs and quantity missing by subtracting the pieces required by the ones owned only if the pieces are owned:
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);
Which results in:
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)