Question 189953 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Returning vertices if the relationship is not present or if property in the edge is less than necessary

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?

Tags:

2

Answers


  1. 0

    You can use exists(path) predicate function to check whether a relationship exists or not. A sample query would be:

    SELECT * FROM cypher('LegoGraph', $$
MATCH (u: User {name : 'Bob'}), (p: Piece) WHERE NOT exists((u)-[]->(p))
RETURN p
$$) AS (result agtype);

    or you want to CREATE the edges in the same query, then use something like

    SELECT * FROM cypher('LegoGraph', $$
MATCH (u: User {name : 'Bob'}), (p: Piece) WHERE NOT exists((u)-[]->(p)) WITH u, 
p CREATE (u)-[e: OWNS {qty: 4}]->(p)
RETURN e
$$) AS (result agtype);
    Login or Signup to reply.
  2. 0

    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, quantity required, and the quantity 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, r.qty, o.qty
$$) AS (piece agtype, qty_required agtype, qty_owned agtype);

    Result:

      piece  | qty_required | qty_owned
---------+--------------+-----------
 242023  | 2            | 2
 302301  | 6            | 2
 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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search