postgresql 如果关系不存在或边中的属性小于必要值，则返回折点

6psbrbz9 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(179)

我创建了一个图表来存储乐高产品，每个产品需要的部件，以及用户已经拥有的部件。顶点有三个标签：Piece、Product和User，边有两个标签：REQUIRES和OWNS。这些顶点按以下方式连接：

(Product)-[REQUIRES {qty: int}]->(Piece {number: int})<-[OWN {qty: int}]-(User)

举个例子，下面是我正在创建的当前图形：

-- 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);

什么是正确的方式来返回的作品（和他们的数量）的乐高设置，用户还没有，使他/她可以建立它？

postgresql

来源：https://stackoverflow.com/questions/76509854/returning-vertices-if-the-relationship-is-not-present-or-if-property-in-the-edge

2条答案

按热度按时间

v9tzhpje1#

您可以简单地MATCH指定产品中所需的件，然后使用OPTIONAL MATCH供所需用户检查，通过减去所拥有的件所需的件来返回缺少的件ID和数量（仅当件被拥有时）：

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);

其结果是：

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)

赞(0）回复(0）举报 2023-06-22

f5emj3cl2#

可以使用exists(path) predicate 函数来检查关系是否存在。示例查询如下：

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

或者你想在同一个查询中创建边，然后使用类似

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);

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql 如果关系不存在或边中的属性小于必要值，则返回折点

2条答案

相关问题

热门标签

最新问答