Pagination features
For paginated queries, rather than concatenating each batch, will sorting the entire returned list server-side ever be supported?
And/or it would be great to be able to apply custom/advanced sorting algorithms once withIndex's have been applied.
Currently, I have my own sorting algorithm applied to just the batch once it reaches the client, to which then I recalculate for every concatenated batch.
16 Replies
I've been creating computed fields to enable complex sorting on paginated queries. Possible for your use case?
You can sort the page before it's returned to the client, and it'll get concatenated. Note that if you reverse the list, the concatenation isn't reversed - so it'll be descending in each page but still ascending for each range, if your original query was ascending
But the sort will still end up per page and not across the whole query right
correct - on the server you only see one page in the query
so you'd have to know that each page has the correct range
I think the OP is specifically asking about sorting the entire list, but still paginating
which points to what you already mentioned about an index on a computed field
yeah paginating on the whole list looks like doing a table scan of the whole thing, sorting it, and returning 1-10 or 11-20 etc. manually. Doable if your table is small enough (<~10k rows)
Hmm, can i see a small example of what you mean?
It's also worth mentioning that you can check out the usePaginatedQuery code to see what it's doing if you want to manage your own pages. You can always do
useQuery
on a paginated query and deal with the pages yourself - just pass the cursor from the previous page to get the next page
I'll get out of the way - @erquhart has good intel here@ian Ah, yes that makes sense. Id rather avoid those costly scans
Thank you both! Appreciate the help.
So you would create/update that score as a field on every write/patch of the threads table, then you can sort on it.
hmm...thats essentially what i'm doing, no?
Are you actually paginating in the client or just collecting all the pages in the client and resorting
The above is inside a convex paginated query, so above is server. On the client, I havent set it up yet, but I could resort whenever a new batch/page appears likeley using a
useEffect
With the method you have above, if your top scoring entry is on page 3, it won't appear in the client until your user gets to the third page, at which point it would pop to the top.
If I'm understanding your setup correctly
The whole list would resort every time a new page is added.
By contrast, if you keep the score updated as a field in the table, you can include that field in the index and sort on it directly, so the order won't change as they page through.
Ah, i think i see what you're saying.
For my algorithm, im weighing several data points at runtime, partly due to how other requirements are setup. I do have several fields that I use for indexing. It's that
filter
you see at the tail of the index definition.
Good thing is, running the algorithm on each batch fits interestingly well for my use case. Just wanted to see if the above things were possible but it doesnt seem like it can be done efficiently (referring to sorting the entire returned list)
i'll try to tinker some more but for now at least on my end im good, and thank you so much for your help. Helped me better understand what i was looking intoGotcha. Yeah only works if computing ahead of time is an option, should have mentioned that. Ian's points above may still be helpful for managing pagination at a lower level.