how to query collected neighbours with pagination support on collected neighbours along with node on which group by is perfromed

[nischalkumarsag]

Neo4j Version: 3.6

For sample graph

create (n1:people{id: 1, name: 'foo1', place: "x"}), (n2:people{id: 2, name: 'foo2', place: "x"}), (n3:people{id: 3, name: 'foo3', place: "y"}), (n20:people{id: 20, name: 'foo20', place: "x"});

create (n4:tag{id: 4, key: "a"}),(n5:tag{id: 5, key: "b"}),(n6:tag{id: 6, key: "c"}),(n7:tag{id: 7, key: "d"}),(n8:tag{id: 8, key: "e"}),(n9:tag{id: 9, key: "f"});

create (n10:meta{id: 10, val: 1}),(n11:meta{id: 11, val: 2}),(n12:meta{id: 12, val: 3}),(n13:meta{id: 13, val: 4}),(n14:meta{id: 14, val: 5}),(n15:meta{id: 15, val: 6}),(n16:meta{id: 16, val: 7}),(n17:meta{id: 17, val: 8}),(n18:meta{id: 18, val: 9}),(n19:meta{id: 19, val: 10});

MATCH (a:people{id: 1}),(b:tag{id: 4}) CREATE (a)-[r:people_tag]->(b);
MATCH (a:people{id: 1}),(b:tag{id: 5}) CREATE (a)-[r:people_tag]->(b);
MATCH (a:people{id: 2}),(b:tag{id: 6}) CREATE (a)-[r:people_tag]->(b);
MATCH (a:people{id: 3}),(b:tag{id: 7}) CREATE (a)-[r:people_tag]->(b);
MATCH (a:people{id: 20}),(b:tag{id: 8}) CREATE (a)-[r:people_tag]->(b);
MATCH (a:people{id: 20}),(b:tag{id: 9}) CREATE (a)-[r:people_tag]->(b);

MATCH (a:people{id: 1}),(b:meta{id: 10}) CREATE (a)-[r:people_meta]->(b);
MATCH (a:people{id: 1}),(b:meta{id: 12}) CREATE (a)-[r:people_meta]->(b);
MATCH (a:people{id: 1}),(b:meta{id: 13}) CREATE (a)-[r:people_meta]->(b);
MATCH (a:people{id: 1}),(b:meta{id: 14}) CREATE (a)-[r:people_meta]->(b);
MATCH (a:people{id: 2}),(b:meta{id: 15}) CREATE (a)-[r:people_meta]->(b);
MATCH (a:people{id: 2}),(b:meta{id: 16}) CREATE (a)-[r:people_meta]->(b);
MATCH (a:people{id: 3}),(b:meta{id: 17}) CREATE (a)-[r:people_meta]->(b);
MATCH (a:people{id: 3}),(b:meta{id: 18}) CREATE (a)-[r:people_meta]->(b);
MATCH (a:people{id: 4}),(b:meta{id: 19}) CREATE (a)-[r:people_meta]->(b);

A people node can be connected to 0 or more tag/,meta nodes. There will be no duplicate edges between any two nodes. Tag and Meta nodes can be interconnected with each other.

I want to select a paginated view of the tag and meta groupped by people. To paginate on neighbors (tag/meta) i change [x..y] To paginate on people I change SKIP and LIMIT . the order of the query should be by attribute id and will always be unique.

For ex: to query for the first two tags, meta of the first two people, I would be using the below query.

MATCH(n:people) WHERE n.place="x"
OPTIONAL MATCH (n)-[x]-(t:Tag) WITH n,t
ORDER BY t.id
WITH n,collect(distinct (t))[0..2] as ct
OPTIONAL MATCH (n)-[y]-(m:meta) WITH n,m,ct
ORDER BY m.id
WITH n,ct,collect(distinct(m))[0..2] as mt
RETURN n,ct,mt
ORDER BY n.id
SKIP 0
LIMIT 2

For ex: to query for the second and third tags, meta of the first two people, I would be using the below query.

MATCH(n:people) WHERE n.place="x"
OPTIONAL MATCH (n)-[x]-(t:Tag) WITH n,t
ORDER BY t.id
WITH n,collect(distinct (t))[1..3] as ct
OPTIONAL MATCH (n)-[y]-(m:meta) WITH n,m,ct
ORDER BY m.id
WITH n,ct,collect(distinct(m))[1..3] as mt
RETURN n,ct,mt
ORDER BY n.id
SKIP 0
LIMIT 2

For ex: to query for the second and third tags, meta of the second and third people, I would be using the below query.

MATCH(n:people) WHERE n.place="x"
OPTIONAL MATCH (n)-[x]-(t:Tag) WITH n,t
ORDER BY t.id
WITH n,collect(distinct (t))[1..3] as ct
OPTIONAL MATCH (n)-[y]-(m:meta) WITH n,m,ct
ORDER BY m.id
WITH n,ct,collect(distinct(m))[1..3] as mt
RETURN n,ct,mt
ORDER BY n.id
SKIP 1
LIMIT 2

is the below query more optimized than above if I don't care ordering on each page, but the pagination will be correct?

MATCH(n:people) WHERE n.place="x"
ORDER BY n.id
SKIP 1
LIMIT 2
OPTIONAL MATCH (n)-[x]-(t:Tag) WITH n,t
WITH n,collect(distinct (t))[1..3] as ct
OPTIONAL MATCH (n)-[y]-(m:meta) WITH n,m,ct
ORDER BY m.id
WITH n,ct,collect(distinct(m))[1..3] as mt
RETURN n,ct,mt

Is there a chance that my pagination is incorrect for either neighbours/people?

Is there a way to achieve similar results in a more optimized way apart from using subquery(only available in version > 4.0) or apoc procedures(requires installation of a plugin)?