Determine number of node relationships in recursive query

[r_chevalier335]

My graph consists Nodes that have CHILD_OF relationships. I'm able to query and generate a tree structure with the following query:

MATCH p = (:Node { name:"Root" })<-[:CHILD_OF *0..]-(c:Node) with *, relationships(p) as i
with REDUCE (path = '1', index IN i | path + '.' + index.index) AS path, c.name as name
ORDER BY path
RETURN { path: path, name: name }

The query returns:

╒════════════════════════════════════╕
│"{ path: path, name: name }"        │
╞════════════════════════════════════╡
│{"path":"1","name":"Root"}          │
├────────────────────────────────────┤
│{"path":"1.1","name":"Sub Assy 1"}  │
├────────────────────────────────────┤
│{"path":"1.1.1","name":"Part 1"}    │
├────────────────────────────────────┤
│{"path":"1.1.2","name":"Part 2"}    │
├────────────────────────────────────┤
│{"path":"1.2","name":"Sub Assy 2"}  │
├────────────────────────────────────┤
│{"path":"1.2.1","name":"Sub Assy 1"}│
├────────────────────────────────────┤
│{"path":"1.2.1.1","name":"Part 1"}  │
├────────────────────────────────────┤
│{"path":"1.2.1.2","name":"Part 2"}  │
├────────────────────────────────────┤
│{"path":"1.3","name":"Sub Assy 3"}  │
└────────────────────────────────────┘

In order to create a new child, I need to determine the number of existing children for the parent node first. When I try to count them I always get a result of one.

MATCH p = (:Node { name:"Root" })<-[:CHILD_OF *0..]-(c:Node) with *, relationships(p) as i
with COUNT ((:Node { ID: c.ID })<-[:CHILD_OF]-(:Node))  as childcount, i, c
with *, REDUCE (path = '1', index IN i | path + '.' + index.index) AS path, c.name as name
ORDER BY path
RETURN { path: path, name: name, childcount: childcount }

Which returns:

╒════════════════════════════════════════════════════╕
│"{ path: path, name: name, childcount: childcount }"│
╞════════════════════════════════════════════════════╡
│{"path":"1","name":"Root","childcount":1}           │
├────────────────────────────────────────────────────┤
│{"path":"1.1","name":"Sub Assy 1","childcount":1}   │
├────────────────────────────────────────────────────┤
│{"path":"1.1.1","name":"Part 1","childcount":1}     │
├────────────────────────────────────────────────────┤
│{"path":"1.1.2","name":"Part 2","childcount":1}     │
├────────────────────────────────────────────────────┤
│{"path":"1.2","name":"Sub Assy 2","childcount":1}   │
├────────────────────────────────────────────────────┤
│{"path":"1.2.1","name":"Sub Assy 1","childcount":1} │
├────────────────────────────────────────────────────┤
│{"path":"1.2.1.1","name":"Part 1","childcount":1}   │
├────────────────────────────────────────────────────┤
│{"path":"1.2.1.2","name":"Part 2","childcount":1}   │
├────────────────────────────────────────────────────┤
│{"path":"1.3","name":"Sub Assy 3","childcount":1}   │
└────────────────────────────────────────────────────┘

Which is incorrect. "Root" should have 3 children and "Sub Assy 1" should have 2.
If I do the count a count for "Root" and "Sub Assy 1" all by themselves I get the correct results.

MATCH (:Node { name:"Root" })<-[i:CHILD_OF]-(c:Node) return count(i)

Correctly returns 3

MATCH (:Node { name:"Sub Assy 1" })<-[i:CHILD_OF]-(c:Node) return count(i)

Correctly returns 2

How do I get the correct childcount in my recursive query?

[intouch_vivek]

@r.chevalier335,

Although it is not a good solution but try

Match (c:Node)-[rel:CHILD_OF]->(d:Node)
With d.name as name , size(collect(rel)) as childCount
Return name, childCount
union
Match (c:Node)-[rel:CHILD_OF]->(d:Node)
With c.name as name , size(collect(rel)) as childCount
Return name, childCount

[r_chevalier335]

Thanks Vivek. On its own that indeed returns the number of children for each parent. How does that fit into my recursive query though?

╒════════════╤════════════╕
│"name"      │"childCount"│
╞════════════╪════════════╡
│"Root"      │3           │
├────────────┼────────────┤
│"Sub Assy 1"│2           │
├────────────┼────────────┤
│"Sub Assy 2"│1           │
├────────────┼────────────┤
│"Sub Assy 3"│1           │
├────────────┼────────────┤
│"Part 1"    │1           │
├────────────┼────────────┤
│"Part 2"    │1           │
└────────────┴────────────┘

[andrew_bowman · Accepted solution]

count() is an aggregation function that works across rows. In your case, you don't really need aggregations. You have the reference to the node of interest, c, already, so what you need is the degree of relationships on c, and we can get that using the size() function:

size((c)<-[:CHILD_OF]-()) as childcount

MATCH p = (:Node { name:"Root" })<-[:CHILD_OF *0..]-(c:Node) 
WITH reduce (path = '1', rel IN relationships(p) | path + '.' + rel.index) AS path, size((c)<-[:CHILD_OF]-()) as childcount, c.name as name
ORDER BY path
RETURN { path: path, name: name, childcount:childcount}

A couple other things that can help...

We can use map projection to simplify your return, and use apoc.text.join() from APOC procedures in place of the reduce() function:

MATCH p = (:Node { name:"Root" })<-[:CHILD_OF *0..]-(c:Node) 
WITH c, apoc.text.join("1" + [rel in relationships(p) | rel.index], '.') as path, size((c)<-[:CHILD_OF]-()) as childcount
ORDER BY path
RETURN c { .name, path, childcount}

[r_chevalier335]

Thank you so much Andrew, you are brilliant! I've been struggling with this for a week.

I have no doubt that if I live long enough, I could have eventually found all of this information in the docs. The challenge I have, and I'm sure others struggle with too, is where to start looking when trying to solve a problem like this. Can you offer any suggestions?

[andrew_bowman]

Here's a link to one of our important knowledge base articles on understanding Cypher cardinality. I would normally link you to the rest of our Cypher knowledge base articles, but we have a little hiccup there that needs fixing first.

I find it often helps when seeing unexpected results or behavior to go through the query and return at various places to check if the results at that point (table or text results) make sense, and to make sure you understand what rows exist at that point, since further operations will execute per row.

You may also want to review the documentation (and make sure the documentation version matches your Neo4j version) for info on how various functions work.

[r_chevalier335]

More great advice. Many thanks. Stay healthy,

[r_chevalier335]

Now I want to return the child node with my query.

type Node {
	ID: ID!
  name: String
  child: [Child]
}

type Child @relation(name: "CHILD_OF") {
	from: Node  #child 	
	to: Node    #parent
	index: String
}

type Tree {
	path: String
	name: String
	indent: Int
	childCount: Int
	node:[Node]  // return the Node object 
}

type Query {
	tree(root: String):[Tree]
		@cypher(statement:
			"""MATCH p = (:Node { name: $root })<-[:CHILD_OF *0..]-(c:Node) 
			WITH c, apoc.text.join('1' + [rel in relationships(p) | rel.index], '.') as path, size((c)<-[:CHILD_OF]-()) as childCount, c as Node
			ORDER BY path
			RETURN c { .name, path, childCount, indent: size(split(path,'.'))-1, Node}"""
		)
}

Again I get a nice result in the the neo4J browser but not so much in the playground.

query
	{tree(root: "Root"){
    path
    indent
    childCount
    node{
      name
    }
	}
}

{
  "errors": [
    {
      "message": "Cannot read property '0' of undefined",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "tree"
      ],
      "extensions": {
        "code": "INTERNAL_SERVER_ERROR",
        "exception": {
          "stacktrace": [
            "TypeError: Cannot read property '0' of undefined",
            "    at buildCypherSelection (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:315:20)",
            "    at recurse (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:87:33)",
            "    at buildCypherSelection (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:173:12)",
            "    at recurse (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:87:33)",
            "    at buildCypherSelection (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:173:12)",
            "    at recurse (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:87:33)",
            "    at buildCypherSelection (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/selections.js:173:12)",
            "    at customQuery (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/translate.js:558:68)",
            "    at translateQuery (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/translate.js:501:12)",
            "    at cypherQuery (/home/rchevalier/dev/grand-stack/api/node_modules/neo4j-graphql-js/dist/index.js:141:40)"
          ]
        }
      }
    }
  ],
  "data": {
    "tree": null
  }
}

What am I missing here?