Pagination with take/skip instead of cursor?
@whoami : Hi team, I am wondering if there is a {take: number, skip: number} (basically the number of elements per page, and the start index where the query starts like here https://www.howtographql.com/typescript-helix/10-filtering-pagination-and-sorting/) API for handling the pagination without cursor? If not, what is the best way for me hack into this? Thanks!
6 Replies
Some tips from Indy already:
Docs on pagination: https://docs.convex.dev/database/pagination
First would like to understand why that doesn't fit your needs so we can improve the api.
Second, if you really did want to do something like limit / skip queries. I believe the best way would be to create an index on a monotonically increasing number column you maintain yourself. https://docs.convex.dev/database/indexes/
We don't have support for built in autoincrement so you'd have to find a way to keep track of the max number yourself (probably by looking at the largest number before inserting in a mutation).
Paginated Queries | Convex Developer Hub
Load paginated queries.
Indexes | Convex Developer Hub
Indexes are a data structure that allow you to speed up your
Since the application is showing a table with multiple pages, just wanting to grab n items per page, the question is how to best support that.
@ballingt asked: there are several options here, are the things you're paging over changing quickly? How many are there? If there aren't hundreds of pages, the infinite scroll-oriented hook could work well for a table too.
I'll also point to a post on our pagination, and how it avoids pitfalls in the take/skip (aka limit/offset) method: https://stack.convex.dev/fully-reactive-pagination
Fully Reactive Pagination
Paginating over large datasets is tricky when the data is changing. Naive approaches result in missing or duplicated data. The trick is to rewrite lim...
At a high level, you have to decide whether you're grabbing a fixed-size page, or if you want it to grow/ expand if there are insertions / deletions within the rows. Here are some options:
1️⃣Fetching fixed pages for append-only data, arbitrary page fetch
As @Indy mentioned, you could make yourself a monotonically increasing index for items, and grab items 1-n, n+1-2*n, etc. for pages.
Upside: You can fetch page m without having to know the cursor from page m-1
Downside: Data can't be inserted / deleted into earlier rankings without messing up page sizes, and you have to manually track that ordering.
2️⃣Keeping a fixed page size for mutating data, page by page
If you want it to stay at exactly N items, then you can use the
db.query(....).take(n) functionality to grab that many items. To grab the next page, you can look at the last item in a page and make your next query for after that item. E.g. if you're querying over the by_creation_time index (the default one), you can query for db.query(...).withIndex('by_creation_time', q => q.gt("_creationTime", prevPageLastItem._creationTime)).take(n). The _creationTime is unique, so you won't miss any items, unless items get inserted between the last item and your new item. This is why our paginate works like it does (see the Stack post for how we avoid missing items)
Upside: The results are always the same length. No manual index tracking needed.
Downside: Insertions and deletions result in missing items or items on multiple pages. You have to fetch one page at a time
3️⃣Allowing page sizes to grow/shrink on insert/delete, page by page
This is how ours works out of the box. When items aren't being inserted / deleted, you get pages in the size you specify. To fetch page 5, you can fetch pages 1-5. To make this more efficient, you could even do this within an action, which would cache each page it fetches, so you could request page 10 and it would fetch the first 10 pages with 10 queries and only return the last page.
Upside: All items are going to show up on exactly one page. No manual tracking required.
Downside: You can't grab page "n" directly without loading the previous pages. Pages may change length.4️⃣Pragmatic fixed size paging for up to 10k items, with page by page after
Being pragmatic, the UX for showing pages of data in a table generally looks like this:
- You show 25 or 50 items on a page, say.
- You show buttons to jump to page 1-10, and a "Next" button. When you click "Next" it loads page 11, and then hitting "Next" loads page 12.
This is how google's results worked before they moved to infinite scroll.
With this UX constraint, you can support take/skip up to the first 10 pages, then use our page-by-page pagination API after that. Here's how:
For the first 10 pages, just do
db.query(...).take(pageSize * n) for page n, returning only the last pageSize number of items. You'll load at most 500 rows, for 10 pages of 50 items, which is fast enough on the server, and only sends 50 items over the public internet to the client at a time, so it'll be a small amount of data.
After that, use a different endpoint that takes in the last item from page 10 - its _creationTime, if you're sorting that way, say. Paginate starting from there, returning a page at a time to load pages 11, 12, 13. You can use a simple useQuery instead of usePaginatedQuery if you want to get each page's results rather than get a unified array of all the results so far. e.g. db.query(...).withIndex('by_creation_time', q => q.gt("_creationTime", lastItem._creationTime)).paginate(opts) - note that lastItem always refers to item 500, so the query parameters don't change.
5️⃣Pragmatic separation of query size vs. display size, fixed size for every page
In the same spirit as (4), you can fetch results in pages much larger than your display pages. You could fetch all 500 items in the first page, say. Then you could show the UI for each page, just jumping to
results[(n-1)*pageSize...n*pageSize]. If the user wants to jump to a page that hasn't been loaded yet, you just keep fetching pages until results.length >= n*pageSize.
Upside: UI pages are always the same size, and items don't get lost - they might just shuffle between UI pages. The underlying fetches are optimized around how much data should be fetched at a time.
Downside: If you have tens of thousands of rows and are jumping to an arbitrary page, you will have to fetch all of the data beforehand.
@whoami I hope this gives you a sense of the options available. Ultimately we make the infinite scroll case the easiest out of the box, but it should allow you to do just about anything. If you did want to jump to an arbitrary point in history, I would also suggest jumping by some logical comparison, rather than just an offset / skip. E.g. filter to before a certain date, or let them pick the date range and do page-by-page from there.