Neo4j: using 'order by' and 'limit' with subqueries

Question

I have a data model that's a tree structure with a max depth of 3. For example:

          (a)       ... first level root node
        /  |  \
       /   |   \
     (b)  (b)  (b)  ... [0..n] number of second depth nodes
    / |         |
   /  |         |
 (c) (c)       (c)  ... [0..n] number of third depth nodes per (b) node

The relationships between the nodes are: (c)-[:in]->(b)-[:in]->(a)

Given a root node (a), I want to create a query that will return the 10 most recent (b) nodes, along with the 3 most recent (c) nodes on each (b) node.

I start here with a query to get the 10 most recent (b) nodes:

match (a) where id(a) = {root_id}
match (a)<-[:in]-(b) where b is not null
return b order by id(b) desc limit 10

This gets the 10 most recent b nodes as expected. However, I can't find a way to get the 3 most recent (c) nodes per (b). This is what I have:

match (a) where id(a) = {root_id}
match (a)<-[:in]-(b) where b is not null
with b order by id(b) desc limit 10
optional match (b)<-[:in]-(c)
return b, c order by id(c) desc limit 3;

However, the limit 3 is applying to the entire return, not just the c subquery.

Is there a way to roll up the (c) subquery such that the limit 3 is applied once for each (b) node?

(I know node ids are not the best to use due to their volatility. I'm just using id() as a quick way to order things in this example)

Answer 1

Your query is almost correct, for the 3 last c nodes foreach b, you can collect them and return only the 3 nodes of the collection :

match (a) where id(a) = {root_id}
match (a)<-[:in]-(b) where b is not null
with b order by id(b) desc limit 10
optional match (b)<-[:in]-(c)
with b, c order by id(c)
RETURN b, collect(c)[0..3] as c

Test it here : http://console.neo4j.org/r/c16wak

Nb: No need to do where b is not null, by using MATCH b will never be null