CODEWITHSUNDEEP
neo4jcypher
Sonu Kapoor
Sonu Kapoor

Reputation: 1637

Get count of each OPTIONAL MATCH

Trying to run the following query:

MATCH(u:User) 
OPTIONAL MATCH (u)-[:USER_TYPE]->(adminType:UserType {type:'Admin'}) 
OPTIONAL MATCH (u)-[:USER_TYPE]->(userType:UserType {type:'User'}) 
RETURN DISTINCT { adminCount: count(adminType), userCount: count(userType) }

This returns me the count of the admins, but the users are 0. If I switch the OPTIONAL MATCH and set the user first, then I get a count for the user, but not the admin. How do I get of both?

Upvotes: 0

Views: 425

Answers (1)

William Lyon
William Lyon

Reputation: 8546

Instead of using OPTIONAL MATCH try this:

MATCH (u:User)-[:USER_TYPE]->(adminType:UserType {type:'Admin'})
WITH count(*) AS adminCount
MATCH (u:User)-[:USER_TYPE]->(userType:UserType {type:'User'})
WITH count(*) AS userCount, adminCount
RETURN {adminCount: adminCount, userCount: userCount}

Match on each pattern then count the number of matches using a WITH clause to bring through only the count.

Edit

As pointed out by @cybersam the above query takes into account the number of relationships, to get the count of UserType nodes with type property values of "Admin" and "User" (without taking relationships into account):

MATCH (adminType:UserType {type:'Admin'}) WITH count(adminType) AS adminCount
MATCH (userType:UserType {type:'User'}) WITH adminCount, count(userType)
RETURN {adminCount: adminCount, userCount: userCount}

Upvotes: 2

Related Questions