Need to display single post of a user on each page Neo4j Cypher

[zhn]

I want to display a single post of each user on each page in descending order by CreatedDateTime.
For example:
If John has created three posts
Post A at 09:00 31-Jan
Post B at 10:00 31-Jan
Post C at 11:00 31 Jan

So the required output shoud be
Page 1 should display Post C
Page 2 should display Post B
Page 3 should display Post A

This means there will be 20 posts for 20 different users on a single page.

So far I wrote this query which displays all the posts on page one.

 MATCH (p:Posts)-[:CREATED_BY]->(u:User)
 WHERE p.CreatedDateTime >= datetime('2021-01-31')
 RETURN p, u
 ORDER BY datetime(p.CreatedDateTime) DESC
 SKIP 0
 LIMIT 20

I know, need to use some subquery but don't have clue how to do that.

TYIA

[dana_canzano]

not sure i completely understand the intended result but if you are looking for guidance on subquery usage see

Neo4j Graph Database Platform

CALL {} (subquery) - Neo4j Cypher Manual

The `CALL {}` clause evaluates a subquery that returns some values.

Neo4j Graph Database Platform

Limiting MATCH results per row - Knowledge Base

Since LIMIT applies to the total number of rows of the query, it can’t be used in cases when matching from multiple nodes where the limit must be on match…

[andrew_bowman]

So each user will only have one post shown, which should be the latest post that user created? And only one post per user?

Here's how you can do this with subqueries:

MATCH (u:User)
CALL {
  WITH u
  MATCH (p:Posts)-[:CREATED_BY]->(u)
  WHERE p.CreatedDateTime >= datetime('2021-01-31')
  RETURN p
  ORDER BY datetime(p.CreatedDateTime) DESC
  LIMIT 1
}
RETURN p, u
// if you need to order these as well, then add another ORDER BY

This uses the approach mentioned in the article Dana posted about Limiting MATCH results per row. The key to this is MATCHing on the user outside the subquery, but performing the MATCH from the user to the posts, as well as the LIMIT of the ordered posts, inside the subquery.

[zhn]

I was trying your answer, this looking helpful. But I am using Neo4j 3.5 version which does not support CALL{} subquery. Can you help me how can I do this in Neo4j 3.5?

[dana_canzano]

My apologies as I was unaware that you were using 3.5.x. I'm not aware of how to achieve this under 3.5.x. Are you not able to use a more recent/current version of Neo4j.