Neo4j Cypher查询:求和两个查询的结果

时间:2022-01-09 23:00:58

I have a graph where I have the following structure:

我有一个图表,我有以下结构:

Neo4j Cypher查询:求和两个查询的结果

Nodes:

  1. Page(BLUE) -> Page is attached to the lectureseries node and lecture by ownerof relation.
  2. 页面(蓝色) - >页面附加到讲座节点和关系所有者讲座。

  3. Lecture Series(PURPLE)-> Lectureseries connected to lecture by seriesof relation
  4. 讲座系列(PURPLE) - >讲座系列通过系列关系连接到讲座

  5. Lecture (GREEN)-> lecture connected to page and lectureseries as mentioned above. lecture have property as public, follower, private and privilege One of the lecture i.e lect1 is connected to the user by a realtion privileged.
  6. 讲座(绿色) - >讲座如上所述连接到页面和讲座。讲座具有公共,追随者,私人和特权的属性讲座之一,即lect1通过特权的实际连接到用户。

  7. User (RED) (here named Ann) - It is connected to page by follows relation and to 1 lecture mentioned above.
  8. 用户(RED)(这里名为Ann) - 按照以下关系和上述1讲座连接到页面。

Initial Condition:

We have to show the user all the public and follower lecture always for that we have the perfect query with no problem we get the required result.

我们必须向用户展示所有公众和跟随者讲座,因为我们有完美的查询,没有问题我们得到了所需的结果。

MATCH
  (o:page{name:'engg'})-[r:ownerof]-(n:lectureseries)-[s:seriesof]-(l:lecture)
WHERE l.privacy='public' or l.privacy='follower'
RETURN DISTINCT n.name as name,n.series_name as title, COUNT(l) AS lecturecount

Result:

name    lecturecount

java    2 (lect3, lect4)

Problem: Now, we have to add those lecture in the count that if the privilege lecture is connected to the user by the relation privileged

问题:现在,我们必须在计数中添加这些讲座,如果特权讲座通过特权关系连接到用户

I tried this query:

我试过这个查询:

OPTIONAL MATCH (o:page {name:'engg'})-[r:ownerof]-(n:lectureseries)-[s:seriesof]-(l:lecture)
WHERE l.privacy='public' or l.privacy='follower'
RETURN DISTINCT n.name as name, COUNT(l) AS lecturecount
UNION
OPTIONAL MATCH (o:page {name:'engg'})-[r:ownerof]-(n:lectureseries)-[s:seriesof]-(l:lecture)-[:privileged]-(u:user {name:'Ann'})
RETURN DISTINCT n.name as name, COUNT(l) AS lecturecount

Results:

name    lecturecount

java    2

java    1

But the result should be a single line: java, 3

但结果应该是一行:java,3

I searched a lot, ended up to the UNION clause, but it's not helping.

我搜索了很多,最后得到了UNION条款,但它没有帮助。

NEW PROBLEM:

How to sum up the result as

如何总结结果为

seriesname  lecturecount             seriescount    lecturecount

java    2                      AS      2             3
dotnet  1

2 个解决方案

#1


2  

I crafted an example dataset based on your figure. (Tip: you can export a CSV from the Neo4j web UI and include it in the question.)

我根据你的身材制作了一个示例数据集。 (提示:您可以从Neo4j Web UI导出CSV并将其包含在问题中。)

CREATE
  (lect1:lecture {name:"lect1"}),
  (lect3:lecture {name:"lect3", privacy: "public"}),
  (lect4:lecture {name:"lect4", privacy: "follower"}),
  (lect5:lecture {name:"lect5"}),
  (engg:page {name:"engg"}),
  (Ann:user {name:"Ann"}),
  (java:lectureseries {series_name:"java"}),
  (engg)-[:ownerof]->(lect1),
  (engg)-[:ownerof]->(lect3),
  (engg)-[:ownerof]->(lect4),
  (engg)-[:ownerof]->(lect5),
  (Ann)-[:follows]->(engg),
  (Ann)-[:privileged]->(lect1),
  (java)-[:seriesof]->(lect1),
  (java)-[:seriesof]->(lect3),
  (java)-[:seriesof]->(lect4),
  (java)-[:seriesof]->(lect5),
  (engg)-[:ownerof]->(java)

Query:

MATCH (:page {name:'engg'})-[:ownerof]->(n:lectureseries)
OPTIONAL MATCH (n)-[:seriesof]->(l1:lecture)
WHERE l1.privacy='public' or l1.privacy='follower'
WITH n, COUNT(l1) as lecturecount1
OPTIONAL MATCH (n)-[:seriesof]->(l2:lecture)<-[:privileged]-(:user{name:'Ann'})
RETURN n.series_name as name, lecturecount1 + COUNT(l2) AS lecturecount

The WITH construct allows you to chain queries together.

WITH构造允许您将查询链接在一起。

Results:

╒════╤════════════╕
│name│lecturecount│
╞════╪════════════╡
│java│3           │
└────┴────────────┘

A couple of remarks:

几句话:

  • Use directed edges in the query. It does not have a great impact performance-wise but it improves readability.
  • 在查询中使用有向边。它在性能方面没有很大的影响,但它提高了可读性。

  • You don't have to name variables that you are not using later. For this reason, I dropped the o and r variables.
  • 您不必命名以后不使用的变量。出于这个原因,我删除了o和r变量。

  • According to the Cypher styleguide, all keywords should be uppercase.
  • 根据Cypher样式指南,所有关键字都应该是大写的。

  • Unless you have multiple ownerof edges between a page and its lecture series, there is not need to use DISTINCT.
  • 除非页面与其讲座系列之间有多个所有者边缘,否则无需使用DISTINCT。

  • Make sure that you carry node n in the WITH clauses, else you'll get a new n variable in the following matches (Thanks InverseFalcon.)
  • 确保在WITH子句中携带节点n,否则您将在以下匹配中获得一个新的n变量(感谢InverseFalcon。)

#2


0  

NEW PROBLEM: -Solved myself posted for others who may stuck like me.

新问题: - 我自己发布了可能像我这样的人。

Match (o:page{name:'engg'}) 
with o 
optional match (o)-[:ownerof]-(ls:lectureseries)-[:seriesof]-(l:lecture) 
where l.privacy='public' or l.privacy='follower' 
with o ,count(distinct(ls)) as lscount,count(l) as lecount
optional match (o)-[:ownerof]-(ls)-[:seriesof]-(l1:lecture)-[:privileged]-(u:user{name:'Ann'})
RETURN lscount as lectureseriescount,lecount+count(l1) as lecturecount

#1


2  

I crafted an example dataset based on your figure. (Tip: you can export a CSV from the Neo4j web UI and include it in the question.)

我根据你的身材制作了一个示例数据集。 (提示:您可以从Neo4j Web UI导出CSV并将其包含在问题中。)

CREATE
  (lect1:lecture {name:"lect1"}),
  (lect3:lecture {name:"lect3", privacy: "public"}),
  (lect4:lecture {name:"lect4", privacy: "follower"}),
  (lect5:lecture {name:"lect5"}),
  (engg:page {name:"engg"}),
  (Ann:user {name:"Ann"}),
  (java:lectureseries {series_name:"java"}),
  (engg)-[:ownerof]->(lect1),
  (engg)-[:ownerof]->(lect3),
  (engg)-[:ownerof]->(lect4),
  (engg)-[:ownerof]->(lect5),
  (Ann)-[:follows]->(engg),
  (Ann)-[:privileged]->(lect1),
  (java)-[:seriesof]->(lect1),
  (java)-[:seriesof]->(lect3),
  (java)-[:seriesof]->(lect4),
  (java)-[:seriesof]->(lect5),
  (engg)-[:ownerof]->(java)

Query:

MATCH (:page {name:'engg'})-[:ownerof]->(n:lectureseries)
OPTIONAL MATCH (n)-[:seriesof]->(l1:lecture)
WHERE l1.privacy='public' or l1.privacy='follower'
WITH n, COUNT(l1) as lecturecount1
OPTIONAL MATCH (n)-[:seriesof]->(l2:lecture)<-[:privileged]-(:user{name:'Ann'})
RETURN n.series_name as name, lecturecount1 + COUNT(l2) AS lecturecount

The WITH construct allows you to chain queries together.

WITH构造允许您将查询链接在一起。

Results:

╒════╤════════════╕
│name│lecturecount│
╞════╪════════════╡
│java│3           │
└────┴────────────┘

A couple of remarks:

几句话:

  • Use directed edges in the query. It does not have a great impact performance-wise but it improves readability.
  • 在查询中使用有向边。它在性能方面没有很大的影响,但它提高了可读性。

  • You don't have to name variables that you are not using later. For this reason, I dropped the o and r variables.
  • 您不必命名以后不使用的变量。出于这个原因,我删除了o和r变量。

  • According to the Cypher styleguide, all keywords should be uppercase.
  • 根据Cypher样式指南,所有关键字都应该是大写的。

  • Unless you have multiple ownerof edges between a page and its lecture series, there is not need to use DISTINCT.
  • 除非页面与其讲座系列之间有多个所有者边缘,否则无需使用DISTINCT。

  • Make sure that you carry node n in the WITH clauses, else you'll get a new n variable in the following matches (Thanks InverseFalcon.)
  • 确保在WITH子句中携带节点n,否则您将在以下匹配中获得一个新的n变量(感谢InverseFalcon。)

#2


0  

NEW PROBLEM: -Solved myself posted for others who may stuck like me.

新问题: - 我自己发布了可能像我这样的人。

Match (o:page{name:'engg'}) 
with o 
optional match (o)-[:ownerof]-(ls:lectureseries)-[:seriesof]-(l:lecture) 
where l.privacy='public' or l.privacy='follower' 
with o ,count(distinct(ls)) as lscount,count(l) as lecount
optional match (o)-[:ownerof]-(ls)-[:seriesof]-(l1:lecture)-[:privileged]-(u:user{name:'Ann'})
RETURN lscount as lectureseriescount,lecount+count(l1) as lecturecount