Reputation: 75
I'm relatively new to Neo4j so I apologize if there is an obvious answer to this question. I have a db with User nodes, Account nodes and ASSIGNED_TO relationships between them. I have a query (below) to get the users and assigned accounts but I also want to get a count of the users found in the same query regardless of the LIMIT/SKIP result. What seems to be happening, is the user count is based on the OPTIONAL MATCH result, not the result of the MATCH query.
I have 3 users and 3 accounts in the database with 2 users assigned to 2 accounts and one user assigned only to one account.
This is the query:
MATCH (user:User) WITH user OPTIONAL MATCH (user)-[assigned:ASSIGNED_TO]-(account:Account) RETURN user, count(user) as userCount, collect(account) as accounts SKIP 0 LIMIT 25
This is the result:
user userCount accounts {id: 2} 1 [{id: 2}] {id: 1} 2 [{id: 2}, {id: 1}] {id: 3} 2 [{id: 1}, {id: 3}]
I want the userCount value to be 3 for all rows. If I change 'count(user)' to 'count(DISTINCT user)' I get 1 for userCount. I want to avoid running 2 separate queries if possible.
Upvotes: 0
Views: 778
Reputation: 589
A collect-unwind pair should do the trick
MATCH (user:User)
WITH collect(user) as users, count(DISTINCT user) as userCount
UNWIND users as user
OPTIONAL MATCH (user)-[assigned:ASSIGNED_TO]-(account:Account)
RETURN user, userCount, collect(account) as accounts
SKIP 0 LIMIT 25
Upvotes: 3
Reputation: 29167
// Get user count
MATCH (user:User) WITH count(user) as userCount
// Get user
MATCH (user:User)
// To optimize a query, first apply the pagination
WITH user, userCount SKIP 0 LIMIT 25
// The other part of query
OPTIONAL MATCH (user)-[assigned:assigned_to]-(account:Account)
RETURN user,
userCount,
collect(distinct account) as accounts
Upvotes: 3