Add a skip option to the cursor pagination

[AlexRMU]

For some reason, in most tutorials and articles about cursor pagination it is written that, unlike offset pagination, cursor does not allow you to jump to the desired page (skip a certain amount). But that's a lie! You can simply perform a normal cursor search and then skip the required amount. Thus, the advantages of the cursor and offset capabilities will remain, and the speed will hardly change (if you do not skip a lot).

https://github.com/neo4j/neo4j/issues/12695 https://arpitbhayani.me/blogs/fast-and-efficient-pagination-in-mongodb/ https://www.mongodb.com/docs/manual/reference/method/cursor.skip/

Even the prisma documentation says that with the cursor pagination, you need to skip 1 row in order not to include it in the result. What prevents you from skipping more?

I suggest adding a skip and includePageCount in the cursor pagination options and changing the meta accordingly.

[deptyped]

Hello, thank you for your interest in this project.

If we were to query information necessary to navigate to a specific page (such as the total number of records to calculate the number of pages), we would encounter the same performance issues as with page-based pagination.

Additionally, we would have functional issues similar to those of regular page-based navigation. Different users would see different information on pages depending on the cursor from which they count pages.

In general, technically it's possible, but it doesn't make sense. We would encounter the issues of page-based pagination and lose the advantages of cursor-based pagination.

[AlexRMU]

Even when jumping to a large number of pages, the speed will be higher, because reading does not start from the beginning, but from the cursor. And after that, the cursor is updated and the offset is not used to go to the next page. Plus, user can limit the maximum size of navigation, for example, to +-5 pages, then there will be no problems at all.

Counting can be slow even by index, but you can use the built-in database tools for approximate calculation, or count only 1 time if the data does not change often.

Many people are ready to accept this.

[deptyped]

But how to determine how many pages are available before and after the cursor? As I understand it, we need to perform page_size * page_count overfetch in both directions to determine the existence of neighboring pages. It seems that this will have a significant impact on performance.

[AlexRMU]

Basically, the user goes to the next page, that is, the cursor pagination is used. And you need to use the offset only at the first request and when jumping, that is, rarely.

I didn't quite understand about overfetch. You need to count how many rows are before and after the cursor, that is, get the count of pages and the current page. Using an index, this will be fine, like getting the last page number in an offset pagination.

I can create this extension and post the code here, for the seekers.

[deptyped]

You need to count how many rows are before and after the cursor, that is, get the count of pages and the current page.

Not possible with Prisma for some queries:

Unable to process combination of query arguments for aggregation query. Please note that it is not possible at the moment to have a null-cursor, or a cursor and orderBy combination that not stable (unique)

I've encountered that limitation before. However, I think we can add this feature; it seems useful in some cases. But I'm not sure how to implement it without findMany and over-fetching. What do you think?

[AlexRMU]

https://github.com/prisma/prisma/discussions/13348

[AlexRMU]

It would also be good to add the ability to pass his resetSelection. And why isn't the usual prisma count used?

[AlexRMU]

You can add the "cursor with offset" option to distinguish this from the usual cursor pagination. If the cursor is null, then use the offset pagination. Otherwise, the usual "cursor with offset" pagination. And use types and documentation to force only unique fields to be used in sorting. Something like this:

import type { Object } from "ts-toolbelt";
import { Prisma } from "@prisma/client";
type ModelKey = "user";
type Model = (typeof prisma)[ModelKey];

type Keys = Object.NonNullableKeys<Parameters<Model["findUnique"]>[0]["where"]>;
type OrderBy = Keys extends infer T ? (T extends string ? { [key in T]: Prisma.SortOrder } : never) : never;
type Arg = {
    orderBy: OrderBy | OrderBy[];
};

[AlexRMU]

You can add an option to get the number of pages. If the data changes rarely, the user will use it only on the first request. It can be used with each request, but the speed will be approximately the same as with offset pagination ( 1 request for the total count vs 1 request for the count before and 1 request for the count after, using indexes )