Get total count and paginated result in one cypher query (neo4j)

Question

I am writing an api to return neo4j data. For my case I get all nodes matching. API takes in userId, limit and offset and return a list of data matching that condition.

I found one solution Cypher to return total node count as well as a limited set but it is pretty old. Not sure if this is still the best way to do it.

Performance is same as firing 2 separate queries, atleast then one of them would be cached by neo4j after couple of runs.

Match(u:WorkstationUser {id: "alw:44807"})-[:HAS_ACCESS_TO]->(p) return distinct(p) skip 0 limit 10 

Match(u:WorkstationUser {id: "alw:44807"})-[:HAS_ACCESS_TO]->(p) return count(distinct(p))

I want the result to be something like

{
  items: [ {},  {}], # query 1
  total: 100,   # query 2
  limit: 10,  # can get from input
  skip: 0    # can get from input
}

InverseFalcon InverseFalcon · Accepted Answer · 2019-01-17T17:43:29

This will depend a bit on how much information you need from the nodes for which you want the count, and whether you need to get distinct results or not.

If distinct results are not needed, and you don't need to do any additional filtering on the relationship or node at the other end (no filtering of the label or properties of the node), then you can use the size() of the pattern which will use the degree information of the relationships present on the node, which is more efficient as you never have to actually expand out the relationships:

MATCH (u:WorkstationUser {id: "alw:44807"})
WITH u, size((u)-[:HAS_ACCESS_TO]->(p)) as total
MATCH (u)-[:HAS_ACCESS_TO]->(p) 
RETURN p, total
SKIP 0 LIMIT 10

However if distinct results are needed, or you need to filter the node by label or properties, then you will have to expand all the results to get the total. If there aren't too many results (millions or billions) then you can collect the distinct nodes, get the size of the collection, then UNWIND the results and page:

MATCH (:WorkstationUser {id: "alw:44807"})-[:HAS_ACCESS_TO]->(p)
WITH collect(DISTINCT p) as pList
WITH pList, size(pList) as total
UNWIND pList as p
RETURN p, total
SKIP 0 LIMIT 10

Get total count and paginated result in one cypher query (neo4j)

1 Answers