CODEWITHSUNDEEP
neo4jcypher
David
David

Reputation: 247

Neo4j: multiple counts from multiple matches

Given a neo4j schema similar to

(:Person)-[:OWNS]-(:Book)-[:CATEGORIZED_AS]-(:Category)

I'm trying to write a query to get the count of books owned by each person as well as the count of books in each category so that I can calculate the percentage of books in each category for each person.

I've tried queries along the lines of

match (p:Person)-[:OWNS]-(b:Book)-[:CATEGORIZED_AS]-(c:Category)
where person.name in []
with p, b, c
match (p)-[:OWNS]-(b2:Book)-[:CATEGORIZED_AS]-(c2:Category)
with p, b, c, b2
return p.name, b.name, c.name,
count(distinct b) as count_books_in_category,
count(distinct b2) as count_books_total

But the query plan is absolutely horrible when trying to do the second match. I've tried to figure out different ways to write the query so that I can do the two different counts, but haven't figured out anything other than doing two matches. My schema isn't really about people and books. The :CATEGORIZED_AS relationship in my example is actually a few different relationship options, specified as [:option1|option2|option3]. So in my 2nd match I repeat the relationship options so that my total count is constrained by them.

Ideas? This feels similar to Neo4j - apply match to each result of previous match but there didn't seem to be a good answer for that one.

Upvotes: 1

Views: 1908

Answers (1)

Luanne
Luanne

Reputation: 19373

UNWIND is your friend here. First, calculate the total books per person, collecting them as you go. Then unwind them so you can match which categories they belong to. Aggregate by category and person, and you should get the number of books in each category, for a person

match (p:Person)-[:OWNS]->(b:Book)
with p,collect(b) as books, count(b) as total
with p,total,books 
unwind books as book
match (book)-[:CATEGORIZED_AS]->(c)
return p,c, count(book) as subtotal, total

Upvotes: 4

Related Questions