CODEWITHSUNDEEP
javaneo4jcypher
Vartika
Vartika

Reputation: 1095

Neo4j Cypher query: Sum the results of two queries

I have a graph where I have the following structure:

Sample graph

Nodes:

  1. Page(BLUE) -> Page is attached to the lectureseries node and lecture by ownerof relation.
  2. Lecture Series(PURPLE)-> Lectureseries connected to lecture by seriesof relation
  3. 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.
  4. User (RED) (here named Ann) - It is connected to page by follows relation and to 1 lecture mentioned above.

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

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

NEW PROBLEM:

How to sum up the result as 

seriesname  lecturecount             seriescount    lecturecount

java    2                      AS      2             3
dotnet  1

Upvotes: 1

Views: 1261

Answers (2)

Vartika
Vartika

Reputation: 1095

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

Upvotes: 0

Gabor Szarnyas
Gabor Szarnyas

Reputation: 5047

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

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.

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.
  • According to the Cypher styleguide, all keywords should be uppercase.
  • Unless you have multiple ownerof edges between a page and its lecture series, there is not need to use 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.)

Upvotes: 5

Related Questions