Vartika Vartika - 20 days ago 5
Java Question

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.

Answer

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