Cursor Based Pagination

[PatrickMunsey]

Cursor based pagination

I've separated cursor based pagination from #3 into it's own issue (this one) to keep the discussion about bother feature sets a bit more docused. My notes from my investigation into cursor based pagination are below

Already Mentioned Points

• Cursor based pagination should be pretty easy to implement. However, we would have to think about where to specify the cursor field name (e.g. "id") - is it something that is specified when creating a DirectFilterPipe (only one cursor type allowed) or something that the client hast to send as well? That's especially important, because you always have to sort by the cursor field (otherwise the results are unpredictable)

Notes

• I think that we should specify cursor name at time of building query (something that the client specifies). Once a findMany() endpoint is configured this will mean less rework for future frontend data request requirements.
• We can either include all the required cursor information in a single encoded string or include just the cursor field + value of the next record with the other information mandatorily included in separate query params already established such as orderBy, skip, and limit.
• If a cursor can be expressed in a single query parameter string then this shouldn't be too hard for clients to deal with especially if the next and previous cursor are returned from the nestjs api in each response.
• The cursor field and orderBy field must always be the same
• If no cursor string is provided, the pagination is initialised form the start or end using the provided sort and take information. A next cursor will be generated and return in the metadata to be considered in the next request.
• If a cursor (field + value) is provided, the string will be added to the query.
• Cursor fields must be within the Prisma.<PrismaModel>WhereUniqueInput Type. My previous point in Select fields and pagination aliases #3 about using Prisma.<PrismaModel>FindManyArgs to initialise the pipe may be useful to be able to extract the Type for the valid cursor field keys and value types. Using the Prisma.<PrismaModel>FindManyArgs Type may give us more information to play with while still only needing to use a single type to initialise the pipe.

Proposed Process

1. create query parameter ?cursor=... using filter. Builder which includes the following information
   • no cursor field or value,
   • order by,
   • take
   • skip
2. Send request with query parameter
3. Extract cursor object into findOptions from request query parameter using Pipe
   • no cursor field or value,
   • order by,
   • take
   • skip
4. Get record from prisma.finMany() using findOptions object
5. Generate next and previous cursors to be included in response metadata using the prisma.findMany() data and previous cursor
   1. use order by, take, and skip from just used query
   2. calculate cursor field and value using first and last records in retrieved data
6. Send response from api to client with requested data and matadata including generated next and previous cursors
7. create query parameter ?cursor=... using querybuilder or using generated next cursor send by api in response metadata notes in step 5 & 6
   • cursor field + value,
   • order by,
   • take
   • skip

Required Information to perform Prisma.findMany() with a cursor

• take value + direction (number, Integer)
• skip value + direction (number, Integer)
• order by (ISingleOrder<TDto>)
  • field: (keyof T & string)
  • dir: (FilterOrder)
• cursor (Prisma.<PrismaModel>WhereUniqueInput | undefined)
  • field (keyof T & string| undefined)
  • value (string | number | string[] | number[] | boolean | null | undefined)

All of this information will need to be able to be held and expressed in the following ways:

• A query parameter called cursor with type string. e.g. ?cursor=<encodedCursorStringValue>
• A set of fields within findOptions that can be set to store cursor information parsed from the query param string. This will then be passed into prisma.findmany() as it already does.

Proposed Functions (To be further explored)

[PatrickMunsey]

Hi,

I've managed to implement cursor pagination as well as transition Pipe initialization to use the Prisma.<PrismaModel>FindManyArgs Type input.

I didn't implement the idea of consolidating all cursor information into a single query string parameter and chose to just use the already available skip, take and orderBy parameters. After a bit of experimentation, I think this approach keeps things a lot easier to understand both from a client and api perspective.

An example query string that would be generated by the new filter.bilder is as follows:

'?limit=2&skip=0&sort[0][field]=id&sort[0][dir]=asc&cursor[field]=id&cursor[value]=3'

This will generate the following prisma query object using filter. Parser

{
    filter: [],
    cursor: { field: 'id', value: 3 },
    sort: [{ field: 'id', dir: 'asc' }],
    limit: 2,
    skip: 0,
  }

For now it will be up to the client to ensure that order and cursor fields are the same and of a valid field. e.g. both using field = 'id' or both using field = 'email'. As long as the field used is within the Prisma.<PrismaModel>WhereUniqueInput Type, then everything works fine.

Would you like me to open a new pull request or wait until the other one in #4 is processed and reviewed before looking at merging the cursor features. The previous pull request was also made to master branch because I was unable to open a new feature specific branch. Let me know if this is an issue and I can submit a new pull request for that issue to a new branch once you've created one.

Happy to answer any questions or discussion and issues further.

Thanks

[Valerionn]

Hey,

first of all: Thank you very much for your contributions! Your implementation sounds good to me - you can already create the second pull request if you would like to. It's also totally fine if they target the master branch directly.

I'm currently about halfway through reviewing #4, and will try to finish my review until the end of this week if possible.

[nikola418]

Any news ?