Vartika Vartika - 20 days ago 5
Java Question

Neo4j-Cypher Query : sum the results of two queries

SAMPLE GRAPH

I have a graph where I have the following structure, You can see it in the attached image.

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

RESULT:-

name lecturecount

java 2

java 1


But the result should be as needed java ---> 3

I searched a lot ended to union but its 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.)