Using pagination in convex vs something like TanStack Tables?
I discovered convex via TanStack. I have a playground project where I got TanStack Tables working with pagination, sorting, and filtering all in one go. Benefits/drawbacks on using that over more hands-on coding with convex pagination and other coding to get the same results (dynamic filtering, sorting, address bar syncing and browser history consistency)?
17 Replies
Thanks for posting in <#1088161997662724167>.
Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets.
- Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.)
- Use search.convex.dev to search Docs, Stack, and Discord all at once.
- Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI.
- Avoid tagging staff unless specifically instructed.
Thank you!
Doing some re-reading of the convex docs and somehow just now found the stack articles on both.
Ok. Convex makes some very complex things very easy and native. Very cool. What I need is maybe more basic but still in convexesse. I want to build this pagination button set under a table. I see the set number of items per page. I see the “there are more items to display.” Is there any convex magic to generate these buttons dynamically based on the number of items returned, or do I rely on more conventional coding?
What comes to mind is calculating total count with aggregate component and calculating number of pages based on total count / page limit
Yeah, I found ways to build it programaticaly based on that, I just didn't know if there was anything already built in to the getPages that would do that.
currently the supported solution is to use the aggregate component. the tricky part isn't necessarily finding the number of pages, it's skipping to find the documents for the page at index i. https://www.convex.dev/components/aggregate#offset-based-pagination
another option if you just want to move back and forth between pages but not jump to an arbitrary page: https://discord.com/channels/1019350475847499849/1019350478817079338/1319351541697871902
Is the sorting for offset pagination limited to _creationTime? I'm using the photos example and putting in the fields I want to sort on, but no matter what I get a TypeScript error saying that the value I want to sort is is not assignable to parameter type _creationTime. None of the fields I'm referencing for namespace, sortKey have anything to do with _creationTime. My indexes are all strings. I'm basically subsituting the _id for the album and the asset_tag for the _creationTime.
const theDevices = new TableAggregate<{
Namespace: string;
Key: string;
DataModel: DataModel;
TableName: "devices";
}>(components.theDevices, {
namespace: (doc) => doc._id,
sortKey: (doc) => doc.asset_tag,
});
export const paginatedDevices = query({
args: { _id: v.string(), offset: v.number(), numItems: v.number() },
returns: v.array(v.string()),
handler: async (ctx, { _id, offset, numItems }) => {
const { key: assetTag } = await theDevices.at(ctx, offset, {
namespace: _id,
});
const devicesRows = await ctx.db
.query("devices")
.withIndex("by_assetTag", (q) =>
q.eq("_id", _id).gte("asset_tag", assetTag),
)
.take(numItems);
return devicesRows.map((device) => device._id);
},
});Ordering is by the fields in the index. And each index implicitly ends with the field
_creationTime. So you can create .index("by_thing", ["albumId", "assetTag"]), then do a query .withIndex("by_thing", q=>q.eq("albumId", albumId)) and this will be ordered by assetTag, and for equal assetTags it will be ordered by creation time.
Your code looks confusing to me. How is by_assetTag defined? It's usually useless to put _id at the beginning of an index, because documents all have unique _id so the rest of the fields become useless. Similarly, making the aggregate namespace be _id means that each document is in its own namespace, so the aggregate component doesn't do any aggregating.It's confusing because I don't know what I'm doing yet. 🙂
From my devices schema - .index("by_assetTag", ["asset_tag"]),
Can the asset_tag be the namespace and the sortKey?
What are you trying to paginate? Do you want to paginate the whole table, sorted by assetTag (and if same asset tag, what is tiebreaker)?
Right now whole table of devices sorted by asset_tag which is unique and never empty
Okay cool. Then you want namespace to be null -- namespace is how you split up your table into completely independent parts
And sortKey can be asset_tag, that makes sense. And you index on asset_tag is also good
OK, sorry, so I'm still missing something obvious. there is something still keying off if _creationTime.
const theDevices = new TableAggregate<{
Key: string;
DataModel: DataModel;
TableName: "devices";
}>(components.theDevices, {
sortKey: (doc) => doc.asset_tag,
});
export const paginatedDevices = query({
args: { asset_tag: v.string(), offset: v.number(), numItems: v.number() },
returns: v.array(v.string()),
handler: async (ctx, { asset_tag, offset, numItems }) => {
const { key } = await theDevices.at(ctx, offset);
const devicesRows = await ctx.db
.query("devices")
.withIndex("by_assetTag", (q) =>
q.eq("asset_tag", asset_tag).gte("asset_tag", key),
)
.take(numItems);
return devicesRows.map((device) => device._id);
},
});Yeah that
q.eq("asset_tag", asset_tag) looks not right. You want just q.gte("asset_tag", key)
As currently written, you're requesting documents that are both == to and >= to asset_tagThank you! I thought that' looked weird. That got me through that set of red squiglies. Thanks so much. Now some other stuff to fix. I really want to be as convex centric as I can. I already had a TanStack Table working, but I'd like to see this work as well. Thanks for your help and patience.
@lee Hey there. I'm back to this. I'm getting a "tree is empty" error. Would you be able to whip up a version of your photos offset-based pagination example, but instead of sorting by _creationTime it was based on the album name? That's basically what I'm doing, whole table sorted by one field that is (in my case) always unique (or maybe first sorted by album name then by _creationTime as a tie breaker). This is what I'm using to call for the data.
const { data } = useSuspenseQuery(
convexQuery(api.devices.paginatedDevices, { offset: 0, numItems: 10 }),
);
Also, once I get results, can I use either the native pagination functions, and/or the getPage helper functions? Or neither and add in some kind of offset+ to generate sets and links to sets of pages of results?
As I think it through, I could get away with an infinite scroll table, technically. In my app this list of devices is in a table. Come to think of it, (not a convex questions strictly) can you make a <table> that is infinite school? Pardon the ignorance, I've just never messed with infinite scroll results before. Offset pagination is a relatively simple prospect in other databases that take things like limit and offset nativly. Not a dis on convex, just a learning curve thing.yeah infinite scroll can work with <li> <table> etc. - if you have a truly large amount of things to scroll you can use "virtualized tables"
Offset/limit is definitely a common practice, but also has performance issues which is partly why we don't offer it out of the box.
That said, folks often get away with this by showing the first N pages as buttons, and do as many paginated queries as are necessary to fill those pages, as they're requested. e.g. you can do a paginated query over 20 items at a time, but show a page of 10 at a time to users, and fetch more pages reactively if they go past page 2 or proactively whenever they load the page.
Which is just to say the UI doesn't have to match the exact request/response.
usePaginatedQuery returns a list, so you can do .slice(10, 20) on it for page 2, e.g.
If you don't have "jump to page N" but just do it like google with a "next" button, you can keep paginating as they click "next" rather than needing to jump to the middle of the results. Needing to jump is where the aggregate component shinesDon’t OFFSET Your SQL Query’s Performance | HackerNoon
To implement pagination without unexpected performance issues on large sets of data, use "WHERE id > N" instead of "OFFSET N”.