I have a node hierarchy like following, where nodes c1..c6 are of type :Category, and their sub-nodes i1..i7 are of type :Item.
What I need to obtain is the number of Items in each Category, including their sub-categories. The output should look like this:
category childCount itemCount c1 5 7 c2 2 4 c3 1 3 c4 0 2 c5 0 1 c6 0 2
Currently I have a query that returns the correct amount of child nodes, but the number of items is only displayed for each node, not summed up in total. Not sure if I'm missing anything here, or if this is not the right approach?
It's important to note that I can't rely on specifying the starting node by any property myself, as it can change over time inside the db, therefore the query should start with the top Category node that does not have a parent.
MATCH p = (c:Category)-[:IS_PARENT_OF *0..]->(c) WITH c, apoc.text.join("1" + [rel in relationships(p) | rel.index], '.') as path, size((:Category)<-[:IS_PARENT_OF*]-(c)) as childCount, size((:Item)-[:IS_CATEGORIZED_AS]->(c)) as itemCount, c.name AS name ORDER BY path RETURN name, childCount, itemCount
Output as it is now:
category childCount itemCount c1 5 0 c2 2 1 c3 1 1 c4 0 2 c5 0 1 c6 0 2